Monday, January 6, 2014

SQL Server database is Case In-Sensitive - is a MYTH!

The case sensitivity or insensitivity depends on the collation applied to the column or at the DB level ..

Normally, the default collation is ‘CI’ but please remember it is not always ‘Case Insensitive’ 

If you run the below query on any particular db server ..

SELECT SERVERPROPERTY('COLLATION')  

what you could retrieve the data value, normally would be, 

Latin1_General_CI_AS  (CI – Case Insensitive, AS – Accent Sensitive)

But it could also be something like this … 
            Latin1_General_CS_AS  (CS – in this case, it is Case Sensitive)        

So though it may take an additional 1/100th of a nano-second, I'd generally prefer to UPPERize the string comparison such as below …


UPPER(SUBSTRING(COMMENT_,1,8)) = 'Schedule

instead of exact match such as

SUBSTRING(COMMENT_, 1, 8) = 'Schedule'

No comments:

Post a Comment