Case sensitive queries in SQL Server

 

By George Mihaescu

 

Summary: this article shows how to perform case-sensitive queries in SQL Server 2000 and above from an application developer perspective.

The problem

By default, a SQL Server installation is case insensitive; this works for most situations, but there are sometimes situations where you want certain queries to be case sensitive (for example, the user names or passwords in a login table – although, to avoid misleading anybody, I should say the I strongly recommend that passwords are stored encrypted in the database).

 

Starting with SQL Server 2000 the default case sensitivity is called "collation", conformant with ANSI SQL 99; a default server installation will have case insensitive collation. However, SQL Server 2000 allows you to specify the collation to be used to the database or even a column level.

Possible solutions

The following solutions will make queries work in case sensitive mode:

 

1.    Store the items on which you want case-sensitive queries in lower case; before running queries, also convert the respective query parameters to lower case. Not recommended; I only list this solution for completeness, as it is not always possible to do this (depending on the application, the casing of the stored items may be important) and even when it is possible, it is far too error-prone.

 

2.    Install SQL Server with case-sensitive collation. Not recommended; again, this solution is listed here for completeness, as in the majority of cases this is not feasible because then you get into the opposite issue of "how do I perform case-insensitive queries?"

 

3.    Do the comparison in your application, using case sensitive comparison functions. Let the database retrieve all the matching records in case insensitive manner, then iterate through the record set and compare the column(s) in question using a case sensitive comparison function. Not a great solution, unless you have just a couple of queries that have to be case sensitive.  

 

4.    Create / modify the columns on which the queries must be case-sensitive so that they use case-sensitive collation. This is a recommended solution if your queries using those columns must always be case-sensitive (as for example may the case with the user names column in a table used for authentication). For example, altering a column:

ALTER TABLE dbo.Users ALTER COLUMN UserID varchar(20) COLLATE SQL_Latin1_General_CP1_CS_AS

Note here the use of the collation SQL_Latin1_General_CP1_CS_AS (as opposed to the default used for the database SQL_Latin1_General_CP1_CI_AS). The difference here is CS (case sensitive) vs. CI (case insensitive). Of course, if you are using a different code page rather than Latin1 General, there will be a CS value for the code page you need.

This change can be done from the SQL Server manager GUI if you don't want to use a SQL script for this.

 

5.    Add case-sensitive collation information to the particular query that must perform in a case-sensitive manner. This is a recommended solution if most of your queries on the columns in question must use the default case-insensitive behavior, but you have occasional ones in which the comparison must be case sensitive. For example:

SELECT * FROM dbo.Users
WHERE
 UserID="george" COLLATE SQL_Latin1_General_CP1_CS_AS
AND
 Password="mihaescu" COLLATE SQL_Latin1_General_CP1_CS_AS


Again, note the use the collation value that uses CS (for case sensitive) and remember to use the applicable CS value for your code page if you are not using the Latin1 General codepage on that table.

An important thing to note here is that you should check the execution plan for your particular query (for example, in Query Analyzer, or using SET SHOWPLAN_TEXT ON before running the query). If the execution plan indicates an index scan, adjust the query so that you get an index seek, for instance in the example above:

SELECT * FROM dbo.Users
WHERE
 UserID="george" COLLATE SQL_Latin1_General_CP1_CS_AS
AND
 Password="mihaescu" COLLATE SQL_Latin1_General_CP1_CS_AS
AND
 UserID="george"
AND

 Password="mihaescu"