Set session to be case sensitive

Question

Is it possible to set the collation for a query so that everything is case sensitive?

Answer 1

If your database and columns use a case-insensitive collation, you cannot specify/force case-sensitive comparison at the session/query level. However, you can do so with a COLLATE clause on query predicates. For example, with case-insensitive column collation Latin1_General_CI_AS:

--case-insensitive will return 'Shaw', 'SHAW', 'shaw', etc.
SELECT *
FROM dbo.Employee
WHERE 
    LastName = 'Shaw';

--case-sensitive will return only 'Shaw'
SELECT *
FROM dbo.Employee
WHERE 
    LastName = 'Shaw'  COLLATE Latin1_General_CS_AS;

Be aware that a COLLATE clause may preclude indexes from being used efficiently and result poor query performance. A way to coerce a case-sensitive compare and leverage indexes too is by including both case-insensitive and case-sensitive predicates. This will allow the index to be leveraged for the case-insensitive predicate and further limit rows with the case-sensitive criteria to further filter the desired rows.

--case-sensitive will return only 'Shaw' and leverage indexes when appropriate
SELECT *
FROM dbo.Employee
WHERE 
    LastName = 'Shaw'
    AND LastName = 'Shaw'  COLLATE Latin1_General_CS_AS;

If all of your queries require case sensitivity, consider a case-sensitive collation (instance, database, and columns). Everything (including object names) will then be case-sensitive.