Sometimes during application development we need to do case sensitive search. Let us see how we can do the same in SQL Server.

For illustration purpose we will use a table called Technology which has a column named Platform.Now let us say column platform has the following values: Dotnet, DotNet, dotnet, DOTNET.

If we run the below query it will return all the four records.

SELECT Platform
FROM Technology 
WHERE Platform= 'dotnet'

In case we want to make the query case sensitive ( for searching 'DotNet')we need to change the collation of the query.

SELECT Platform
FROM Technology 
WHERE Platform COLLATE Latin1_General_CS_AS = 'DotNet'

Adding COLLATE Latin1_General_CS_AS makes the search query case sensitive.However the default Collation of the SQL Server installation SQL_Latin1_General_CP1_CI_AS is not case sensitive.

In case we want to change the collation of any column for any table permanently we need to run the below query.

ALTER TABLE Technology 
ALTER COLUMN Platform VARCHAR(30)
COLLATE Latin1_General_CS_AS

For knowing the collation of SQL Server database we can use the below query:

SELECT DATABASEPROPERTYEX('POC', 'Collation')

For knowing the database column collation we can use the below query:

SELECT name, collation_name
FROM sys.columns
WHERE OBJECT_ID IN (SELECT OBJECT_ID
FROM sys.objects
WHERE type = 'U'
AND name = 'Technology')