SQL Server case insensitive queries

Question

I want to ignore case when comparing string in my queries using SQL Server. So far I am able to do it using something like this:

SELECT *
FROM Venue
WHERE
   Name COLLATE Latin1_general_CI_AI Like '%cafe%' COLLATE Latin1_general_CI_AI

Is there a way to set a global directive so that it would effect every query? Something like this:

SET COLLATE Latin1_general_CI_AI;
SELECT *
    FROM Venue
    WHERE
       Name Like '%this%';
SELECT *
    FROM Venue
    WHERE
       Name Like '%that%';
...

Thank you!

Answer 1

Is there a way to set a global directive so that it would effect every query?

No.

Collation is not a session property that applies to queries, and it cannot be changed dynamically.

The other problem with this request is that case-sensitivity is not an option that can be enabled or disabled by itself: it is a property of a collation, just like accent-sensitivity, width-sensitivity, what order the particular alphabet is arranged in, etc. A query can compare multiple fields, each with a different collation. So even if you could set a collation that would be in effect for the session, that would potentially force columns of other collations to convert collations on the fly when not even being requested to be case-insensitive. A global session setting would also affect sorting (i.e. TOP(n), ORDER BY, etc) and not just comparisons.

Since the issue is that a user wants to determine per execution whether or not to ignore part of the collation, there are a few options, but all will incur some performance penalty:

1. Construct the query (or queries) in Dynamic SQL:

   DECLARE @SQL NVARCHAR(MAX),
           @Collation NVARCHAR(50);
   
   SET @Collation = '';
   IF (@CaseInsensitive = 1)
   BEGIN
     SET @Collation = N'COLLATE Latin1_general_CI_AI';
   END;
   
   SET @SQL = N'SELECT *
     FROM Venue
     WHERE Name ' + @Collation + N' LIKE ''%' + @SearchParam
                  + N'%'' ' + @Collation;
   
   EXEC(@SQL);
   

2. Translate each character into upper-case and lower-case pairs in single-character ranges. This can be done in the app layer for the parameter value being searched on:

   • For default case-sensitive (don't do anything):
     @SearchParam = 'This'
   • For case-insensitive:
     @SearchParam = '[tT][hH][iI][sS]'

3. Force everything to the same case. Assume that the option to do case-insensitive is an additional parameter that is passed in:

   SELECT *
   FROM Venue
   WHERE CASE @CaseInsensitive
           WHEN 1 THEN LOWER(Name)
           ELSE Name
         END
            LIKE 
         CASE @CaseInsensitive
           WHEN 1 THEN '%' + LOWER(@SearchParam) + '%'
           ELSE '%' + @SearchParam + '%'
         END;
   

   Or, do the LOWER() prior to the query:

   IF (@CaseInsensitive = 1)
   BEGIN
     SET @SearchParam = LOWER(@SearchParam);
   END;
   
   SELECT *
   FROM Venue
   WHERE CASE @CaseInsensitive
           WHEN 1 THEN LOWER(Name)
           ELSE Name
         END
            LIKE '%' + @SearchParam + '%';