Today, I have faced a very simple but still annoying problem. I was asked by a client that queries are not working correctly with given WHERE clause. Actually database was design as case insensitive but SOMEHOW on application side there were some business logics which client needs case sensitive inputs (Just like password) and comparisons. 

Here is an example for easy understanding. We have following simple table and data.

USE AdventureWorks
GO
CREATE TABLE #CaseInSensitive
(cisId INT identity(1,1), cisText varchar(50))

INSERT INTO #CaseInSensitive (cisText)
SELECT 'Abc'
UNION ALL
SELECT 'ABc'
UNION ALL
SELECT 'ABC'
UNION ALL
SELECT 'AbC'

If we need all records where column cisText value is ‘ABC’ then here is simple query

SELECT * fROM #CaseInSensitive
WHERE cisText = 'ABC'



Ops. We got all four records as output but we need only one records where cisText = ‘ABC’. But at the time of table creation we have not set any case sensitive collation. Don’t worry; here is a query which can help us.

SELECT * fROM #CaseInSensitive
WHERE cisText COLLATE SQL_Latin1_General_CP1_CS_AS = 'ABC'


During table design we can also make a column case sensitive

CREATE TABLE #CaseSensitive (csId INT identity(1,1), csText varchar(50) COLLATE SQL_Latin1_General_CP1_CS_AS)