Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

Does anyone know if MS SQL Server text queries are optimised for upper or lower case strings?

I read somewhere the it was uppercase, but cant seem to find the reference to it again. Of course if this is entierly wrong and no optimisation takes place, this would also be useful to know.

Thanks for your help.

CM

share|improve this question
Apparently you can do it, and as other people have pointed out this is an install option do do with collation. My idea was to improve the performace of some queries, but apparently having a case sensative collation can cause problems, if you say miss a capital letter, the query will perform much worse than if you simply opted for a case insensative collation. But thanks again all. – CatchingMonkey Mar 30 '12 at 15:17
And when the 2 days that remain for me to accept the answer expire, I will. – CatchingMonkey Mar 31 '12 at 9:54

2 Answers

up vote 3 down vote accepted

I would say it depends on your install of SQL server. The default ones that I have seen are case insensitive.

Run select serverproperty('SQLsortorderName') if you get back nocase_iso it would mean that your searches are case insensitive. So using UPPER would give you the same results, but it would add a bit more work on the CPU.

share|improve this answer
And what if you get "bin_ascii_8"? – CatchingMonkey Mar 30 '12 at 14:16
1  
I've voted up this answer, but I want to make it clear that it's potentially more than just "a bit more work". Depending on the collation, you could end up doing index or even table scans instead of index seeks if you apply an UPPER() or LOWER() to an indexed field in a search condition. – Randolph West Mar 31 '12 at 5:08
up vote 3 down vote

Since the default installations of SQL Server give you case insensitive searches, I am going out on a limb here and say that there is no upper or lower case optimization for searches.

Always adding UPPER() or LOWER() to convert text during a query will impact performance, depending on the volume of data being queried. Do not use UPPER() or LOWER() if you don't have to.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.