Case Sensitive and Case Insensitive Collation in SQL Server explained

Case sensitive collation considers the uppercase and lowercase versions of letters to be identical for sorting purposes.

Here is an example to test Case sensitive (CS) and Case insensitive (CI) collation difference.

We will compare values 'M' and 'm' in a Case insensitive collation:

IF 'M' = 'm' COLLATE Latin1_General_CI_AI
SELECT 'Values are the same'
ELSE
SELECT 'Values are different'

Here is the result:

...which means that comparing 'M' to 'm' in a Case insensitive collation treats the uppercase and lowercase versions of letters to be identical.

Then we will compare values 'M' and 'm' in a Case sensitive collation:

IF 'M' = 'm' COLLATE Latin1_General_CS_AI
SELECT 'Values are the same'
ELSE
SELECT 'Values are different'

Here is the result:

...which means that comparing 'M' to 'm' in a Case sensitive collation treats the uppercase and lowercase versions of letters differently.

See also:
Accent Sensitive and Accent Insensitive Collation in SQL Server explained
Width Sensitive and Width Insensitive Collation in SQL Server explained