Case Sensitive Search on a Case Insensitive SQL Server

In this post i would like to show you, how to implement case sensitive search on
a case insensitive SQL Server column.


First let me create a temp table:

--------------------------------
create table #EMP
(ID int, Name VARCHAR(30));



Insert into #EMP
Select 1, 'vijaya kadiyala' UNION ALL
Select 2, 'Vijaya Kadiyala' UNION ALL
Select 3, 'VIJAYA KADIYALA';


Now run the below query:


SELECT * FROM #EMP WHERE Name = 'VIJAYA KADIYALA'


This query retrieves all the records as shown in below figure:

The string literal what i passed to the query is in Upper case and i am expecting only the row which satisfies this condition. But by default SQL Server Collation attribute is Case insensitive. So SQL Server ignores the case of the string literal.

You can find the collation information by invoking the following procedure.

SELECT DATABASEPROPERTYEX('AdventureWorks2008', 'Collation')

The default collation property value is SQL_Latin1_General_CP1_CI_AS. The text which is Highlighted in green color indicates Case Insensitive.

Now to convert the query into case sensitive query, just add collation property as shown below.

SELECT * FROM #EMP WHERE Name = 'VIJAYA KADIYALA' COLLATE Latin1_General_CS_AS

Now the query is retruning only one row.