Sql-server – Can database objects be made case insensitive while keeping strings case sensitive

case sensitivecollationsql serversql server 2014

In Microsoft SQL Server (2014) it is possible to choose between a case insensitive and case sensitive collation.

My reason for using a case-sensitive collation would be to make "test" = "TEST" return false.

Yet, what I would like to preserve is that when a table "TEST" exists, writing a query like select * from test; would still work. When the database has a case-sensitive collation it doesn't, because I would need to write it like select * from TEST;

Is there a way to set "object collation" and "string collation" separably?

Best Answer

Mostly "yes", depending on how "automagic" you want everything to work.

First: Database identifier Collation (i.e. object names, column names, index names, etc) is taken from the Database's default Collation. That will determine the Collation of columns such as sys.objects.name, etc. So to do this, you can create or alter the Database to have a case-Insensitive Collation, such as Latin1_General_100_CI_AS_SC. This will allow you to have a table TEST that will be valid in queries such as select * from test;.

Server-scoped identifier Collation (e.g. Database names) is handled by the Instance's default Collation.

Second: If a Database's default Collation is case-Insensitive, then expressions that are entirely string literals and/or variables and/or output-parameters and/or UDF return values will be treated as case-Insensitive. For these expressions you would need to override that default behavior, per expression, using the COLLATE clause.

For example:

/* take database default collation behavior */
IF ('test' = 'TEST')
BEGIN
  PRINT 'Case-Insensitive';
END;
ELSE
BEGIN
  PRINT 'Case-Sensitive';
END;
-- ? result depends on the default collation of the "current" database ?


/* force case-sensitive */
IF ('test' = 'TEST' COLLATE Latin1_General_100_CS_AS_SC)
BEGIN
  PRINT 'Case-Insensitive';
END;
ELSE
BEGIN
  PRINT 'Case-Sensitive';
END;
-- Case-Sensitive


/* force case-insensitive */
IF ('test' = 'TEST' COLLATE Latin1_General_100_CI_AS_SC)
BEGIN
  PRINT 'Case-Insensitive';
END;
ELSE
BEGIN
  PRINT 'Case-Sensitive';
END;
-- Case-Insensitive

Expressions between string columns and either string literals or variables will use the Collation of the string column, and if the Collation for the column is case-sensitive, then the expression will be evaluated as case-sensitive without needing to use the COLLATE clause.

By default, newly created columns that do not specify the COLLATE clause will inherit the Database's default Collation. In this case, you will need to specify something like COLLATE Latin1_General_100_CS_AS_KS_WS_SC for all VARCHAR / NVARCHAR columns that you want to be sensitive across all 4 categories (plus support Supplementary Characters).

PLEASE NOTE: Be very cautious about using a binary Collation (i.e. Latin1_General_100_BIN2) to get an accent/case/kana/width sensitive Collation; binary Collations are not truly case-sensitive.

Related Question