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"