Case Sensitive Search in SQL Server

We all know that SQL is a case insensitive language. Yes, it is true. But we think that even in searching the data in database is also case insensitive. But that's not true. There lies a feature called COLLATION which contradicts our assumption.

Collation comes into action when we use ORDER BY clause. There are several collations which define language and sorting according to the alphabet of that language. The default collation we see in the English version of SQL Server on the English version of Microsoft Windows is Latin collation. As English is derived from Latin, the sorting of the English records in the database is performed according to the English alphabet. This Latin collation of two types, case sensitive and case insensitive. The default collation taken by SQL Server during its installation is Case Insensitive Latin collation. If case sensitive Latin collation is selected then that doesn't allow case insensitive searching of the records.

Let's see it practically...

USE AdventureWorks2012

GO

CREATE TABLE SENSITIVE_SEARCH (NAME VARCHAR(100))

GO

INSERT INTO SENSITIVE_SEARCH VALUES ('YASHWANTH'),('yashwanth'),('Yashwanth'),('yasHwanth')

GO

SELECT * FROM SENSITIVE_SEARCH

Now, I've created a table and inserted the same name in different styles.

Now let's do a search with a predicate,

SELECT * FROM SENSITIVE_SEARCH WHERE NAME = 'YASHWANTH'

See the result. It's same as above. Though you change the style of the predicate column value the result lies same.

To achieve case sensitive search, you need to change the collation. Collation can be changed at instance level, database level and column level. In this case, only for this table we need to achieve case sensitive search. So, changing the column's collation is enough.

Before changing the collation, let's know how to know collation at different levels...

1) To know the collation of the server through Object Explorer, right click on the instance name and select Properties.

Through T-SQL, collation can be known as,

SELECT SERVERPROPERTY('collation')

2) To know the collation of the database through Object Explorer, right click on the database and select Properties. Under Maintenance section, collation appears.

Through T-SQL, it can be known as,

SELECT DATABASEPROPERTYEX('AdventureWorks2012','collation')

or connect to the database and run this command

SELECT collation_name FROM sys.databases WHERE name = 'AdventureWorks2012'

3) To know the table collation through Object Explorer, right click on the Table and select Properties. Under Extended Properties, collation appears.

4) Collation of a column can be known through Object Explorer by right clicking on the column and selecting Properties.

Through T-SQL, it can be known as,

SELECT collation_name FROM sys.columns WHERE object_id = OBJECT_ID('SENSITIVE_SEARCH') AND name = 'NAME'

Now we need to change the collation of the column NAME in the table SENSITIVE_SEARCH. To facilitate case sensitive search, we need to choose a suitable collation. To know the available collations supported by SQL Server, run the following query.

SELECT * FROM sys.fn_helpcollations() WHERE name LIKE 'SQL%'

There are some characteristics to be noted while selecting a collation,

_CS - Case Sensitive

_CI - Case Insensitive

_AS - Accent Sensitive

_AI - Accent Insensitive

_KS - Kana Sensitive

_WS - Width Sensitive

In our present case, we require the following collation,

SQL_Latin1_General_CP1_CS_AS

To change the collation of our table's column to the above collation run the following command,

ALTER TABLE SENSITIVE_SEARCH ALTER COLUMN NAME VARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS

Now case sensitive search is enabled for the column NAME.

SELECT * FROM SENSITIVE_SEARCH WHERE NAME = 'YASHWANTH'

GO

SELECT * FROM SENSITIVE_SEARCH WHERE NAME = 'Yashwanth'

To know more about collations in SQL Server, follow the official documentation of Microsoft.

http://msdn.microsoft.com/en-us/library/ms143726.aspx