Set session to be case sensitive

Benjamin Shaw 141 Reputation points
2021-07-14T10:13:12.867+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,102 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,581 questions
0 comments No comments
{count} votes

Accepted answer
  1. Dan Guzman 9,221 Reputation points
    2021-07-14T11:55:45.427+00:00

    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.

    0 comments No comments

0 additional answers

Sort by: Most helpful