Do Functions alter the effectivness of Indexes?

greenspun.com : LUSENET : SQL Server Database Administration : One Thread

I have a Users table with a "LastName" field in it. I index the "LastName" field. In one of my search pages I allow the user to search by Last Name. Would the following query use the Index or not?

SELECT U.* FROM Users U WHERE UPPER(U.LastName) = UPPER(vLastName)

If the answer is "No it does not use the Index", then I please answer the following:

If an application performs many queries that have WHERE clauses against VARCHAR fields, which of the following would you recommend?

A.) Store all of your data in one CASE or the other. (Upper/Lower) And use some sort of InitCap function to CAP the 1st letter of each word when you display (ex: Cities or States).

B.) Configure your DB with a sort order of CaseInsensitive(default). And forcing CaseSensitive comparisons where needed. (ex: UserName)

C.) Or store the Data as CaseSensitive and use the Functions whenever you perform a search on a VARCHAR field.

D.) Your solution.

Thanks in Advance!

John Campos *********** Bonus Question. How does the LIKE clause affect the use of Indexes? ***********

-- Anonymous, August 04, 1999

Answers

John,

I believe that your query will not use the index. To be sure you can test it on your case-sensitive server. I can not test it because my servers are case-insensitive. My belief that the query will not use the index comes from reading usenet postings.

My recommendation is most like A. I would convert any data put in your databases to upper case (as well as converting all the data already in the databases). I would not muck around with an InitCap function; it is likely to have trouble with names like O'Brian, and McDonald anyway. I would just display names in uppercase.

In fact, I have done exactly as I am recommending. That is, I have previously used servers with a case-sensitive sort order, converted input to uppercase, and displayed it in upper case. The users were not at all bothered by the case issue. In fact, I believe people typing data in prefer not having to worry about capitialization.

Bonus Answer:

This paragraph from the SQL Server Books Online article titled, "Wildcards and the LIKE Clause" explains how the LIKE clause affects the use of indexes thusly:

Another important consideration in using wildcards is their effect on performance. If a wildcard begins the expression, an index cannot be used. (Just as you wouldn't know where to start in a phone book if given the name '%mith', not 'Smith'. You'd have to search the whole book!) A wildcard in or at the end of an expression does not preclude use of an index (just as in a phone book, you'd know where to search if the name was 'Samuel%', regardless of whether the names Samuels and Samuelson are both there).

Good Luck,

Eric

-- Anonymous, August 10, 1999


Moderation questions? read the FAQ