How to perform case sensitive comparison in sql server

How to make or perform case sensitive search or comparison in WHERE clause in sql server

Sql sever default comparison is case insensitive. For example suppose we have Customer table

CustomerID	CompanyName	Country
1	Microsoft	usa
2	IBM	USA

  

SELECT CustomerID,CompanyName

FROM Customer

WHERE Country = 'USA'

Output: 

CustomerID	CompanyName
1	Microsoft
2	IBM

We can observe comparison is case insensitive. It is due to default collation SQL_Latin1_General_CP1_CI_AS which is case insensitive.   We can make it case sensitive by using case sensitive collation. For example:

SELECT CustomerID,CompanyName

FROM Customer

WHERE Country = 'USA' COLLATE SQL_Latin1_General_CP1_CS_AS

Output:

CustomerID	CompanyName
2	IBM

Note:  SQL_Latin1_General_CP1_CS_AS is case sensitive collation

SQL server interview questions and answers with explanation
Width sensitive and insensitive collation examples in sql server.
Ascent sensitive and insensitive collation example in sql server.
Case sensitive and insensitive collation examples in sql server.