I have been specializing in building web-based applications since 1998, primarily using HTML, Flex, LiveCycle, Java, ColdFusion, and SQL Server.
You found yourself here, so hopefully you'll get something good out of it!
Enjoy...

Monday, January 08, 2007

Case sensitivity in SQL Server

Microsoft SQL Server by default is case-insensitive which for the most part is very handy when searching and sorting data. There are times though where this isn't ideal, such as managing usernames or passwords.

If you take a look at the DDL of a table, you'll notice that most of your text-based columns including char and varchar fields use "COLLATE SQL_Latin1_General_CP1_CI_AS". If you would like certain columns to be case sensitive you will want to use "COLLATE SQL_Latin1_General_CP1_CS_AS" for your column definition. Please note the substitution of CI for CS in the collation statement.

If you don't have the option to change your column definition, there are a few other options to consider such as casting your columns to varbinary values when performing searches.