Case sensitive and insensitive collation examples in sql server

In collation name meaning of CI and CS

CI: Case insensitive

CS: Case sensitive

Example of case insensitive comparison in sql server

IF 'a' = 'A' COLLATE Latin1_General_CI_AI

    SELECT 'Case insensitive'

ELSE

    SELECT 'Case sensitive'

Output:  Case insensitive

Example of case insensitive comparison in sql server

   

IF 'a' = 'A' COLLATE Latin1_General_CS_AI

    SELECT 'Case insensitive'

ELSE

    SELECT 'Case sensitive'

Output:  Case sensitive

Difference between actual execution plan and estimated execution plan in sql server
Best ways to pass comma delimited varchar variables to IN clause sql server