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.

7 comments:

  1. Anonymous2:28 PM

    Hey, your post turned up when I did a google search - this is exactly the info I was looking for! Thanks!

    BPS in Colorado

    ReplyDelete
  2. Anonymous5:11 AM

    dito

    ReplyDelete
  3. Anonymous1:52 AM

    Good information....Thanks a lot..

    ISMAIL
    Techgate Technologies

    ReplyDelete
  4. Anonymous5:46 PM

    yeeeeeup. worke for me too. awesome.

    ReplyDelete
  5. Precisely what I needed, thanks.

    ReplyDelete
  6. The varbinary tip rocks! After hours of fails using COLLATE, a simple cast to varbinary works.

    ReplyDelete
  7. thanks a lot..for
    exact sol

    ReplyDelete