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.
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.
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"