I got a question recently from a friend about doing case sensitive searches in SQL and I did not know the answer !! I knew that ORM’s like Django have operators such as ILIKE to do this, but I do not know how to do this in raw SQL. So I started looking for ways to achieve this and found out quite a lot about this topic.
Doing case sensitive searches depend on the database used. For instance, PostgreSQL provides an ILIKE operator, whereas MySQL and MS SQL does not. Of course, the ILIKE operator is just a PostgreSQL extension and it’s not in the SQL standard.
MS SQL Server uses collations to control the storage of character strings and then I came across this article which gave the solution to my problem: use the SQL_Latin1_General_CP1_CS_AS collation to do case sensitive searches. The ‘CS’ in the collation specifies that it’s a Case Sensitive look up. There is an equivalent SQL_Latin1_General_CP1_CI_AS (default collation setting) where ‘CI’ corresponds to Case Insensitive look up.
The default collation set in SQL Server can be obtained by running the query:
SELECT SERVERPROPERTY(N'Collation'). And there are more such collations found here (available in SQL Server 2008).
One of the comments in the article, which gave me the solution, explained about the effect of collations on indexes. The post goes on demonstrating how the indexes are left unused when we apply collations on the columns, how to force the indexes to be used and concludes by proving the huge performance improvement.