Case Sensitive Search in SQL Query (Where)

Normally, in programming languages like C# .NET, we need to explicitly add criteria to ignore case sensitive search or compare operations but whereas in SQL Server, search operation by using where query is not case sensitive by default. So to make case sensitive where query, we need to add explicit criteria which is SQL Collation.

SQL Collation

SQL Collation is a clause that can be applied to a database definition or a column definition to define the collation, or to a character string expression to apply a collation cast. It encodes the rules governing the proper use of characters for either a language, such as Greek or Polish, or an alphabet such as Latin1_General (the Latin alphabet used by western European languages).

Default Collation of the SQL Server installation is SQL_Latin1_General_CP1_CI_AS and this is not case sensitive. Consider the UserTable with following names
Morgan
morgan
MorgaN

--Create Test Table
CREATE TABLE UserTable(
UserID int, UserName varchar(250))
 
--Insert rows into Table
Insert into UserTable values(1,'Morgan')
Insert into UserTable values(2,'morgan')
Insert into UserTable values(2,'MorgaN')

If we run below Query,

Select * from UserTable where UserName = 'morgan'

it will returns all the rows, since all the rows contains same data and record search is not case Sensitive.

 To get only case sensitive records you need to change collation of the UserName column. Default Collation of the SQL Server installation SQL_Latin1_General_CP1_CI_AS and this is not case sensitive.

This is new query to get results with case sensitive operation.

Select * from UserTable where UserName COLLATE Latin1_General_CS_AS = 'morgan'

Thanks,
Morgan
Software Developer