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