Case Sensitivity in Selects - Part 1
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/aw
-
Great article - what about some suggestions for performing case insensitive searches on case sensitive databases? Is the best option here to just use UPPER?
Regards,
Scott
-
Excellent article and the timing couldn't have been better! I am in the process of checking into these exact issues! Keep up the good work.
Regards,
Darrell
Darrell Parrish
La Crosse, WI -
I think this is more related to "using functions on an indexed column in a where clause" than to actually collation:
WHERE clauses that perform some function on a column are non-sargable, which means that an index on the column cannot be used, which can hurt performance. On the other hand, if you can rewrite the WHERE clause so that the column and function are separate, then the query can use an available index, greatly boosting performance. for example:
Function Acts Directly on Column, and Index Cannot Be Used:
SELECT member_number, first_name, last_name FROM members
WHERE DATEDIFF(yy,datofbirth,GETDATE()) > 21
Function Has Been Separated From Column, and an Index Can Be Used:
SELECT member_number, first_name, last_name FROM members
WHERE dateofbirth < DATEADD(yy,-21,GETDATE())
Each of the above queries produces the same results, but the second query will use an index because the function is not performed directly on the column, as it is in the first example. The moral of this story is to try to rewrite WHERE clauses that have functions so that the function does not act directly on the column.
Vlad
-
I've been trying to figure out how to do case-sensitive queries forever. However using the example doesn't produce the expected results.
As an example, if I query a field used to store passwords and the password in question is 'DoLPhin', if I use 'dolphin' in the select statement then I get the row returned anyway. Any ideas on why this might be happening?
Thanks,
Edward
-
the CHECKSUM() function looks like a good candidate to investigate.
where CHECKSUM(Col) = CHECKSUM(Val)
-
I tried that, and it didn't work, but I checked the BOL and they had a reference in there to BINARY_CHECKSUM while they were talking about CHECKSUM. Turns out that checksum comparisons on strings in case insensitive databases will be equal. If you perform a BINARY_CHECKSUM it works correctly. Thanks for the tip though, and I have to give props to BOL again.
Edward
-
I've got a follow up coming with some more research. Will check on the code that didn't work, maybe I goofed!
-
Nice adaptation!
This is the same technique many use with an indexed computed CheckSum() or Binary_CheckSum() column...
... where [Computed ChkSum]=CheckSum(ColName) and ColName='A B C Company'
PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply