Case Sensitive Search

If Col1 of Table_XYZ has following values ‘CaseSearch, casesearch, CASESEARCH, CaSeSeArCh’, following statement will return you all the four records.

SELECT Col1
FROM Table_XYZ
WHERE Col1 = 'casesearch'

To make the query case sensitive and retrieve only one record (“casesearch”) from above query, the collation of the query needs to be changed as follows.

SELECT Col1
FROM Table_XYZ
WHERE Col1 COLLATE Latin1_General_CS_AS = 'casesearch'

Adding COLLATE Latin1_General_CS_AS makes the search case sensitive.

Default Collation of the SQL Server installation SQL_Latin1_General_CP1_CI_AS is not case sensitive.

To change the collation of the any column for any table permanently run following query.

ALTER TABLE Table_XYZ
ALTER COLUMN Col1 VARCHAR(20)
COLLATE Latin1_General_CS_AS

To know the collation of the column for any table run following Stored Procedure.

EXEC sp_help DatabaseName

Second results set above script will return you collation of database DatabaseName.