SQL Server 2014 Case Sensitivity issue

Question

I am migrating a database and etl from MySQl to SQL Server and have hit a case sensitivity issue. In MySql the DB is setup as case sensitive because one of the applications we are loading from has codes like 'Divh' and 'divh' in the one set (its not my doing) all is well and the select statements all over the place in etl, queries reports etc have all used whatever the author wanted regarding case - some are all UPPER some all lower most mixed. So, in other words MYSql has case-insensitive DDL and SQL but allows case sensitive data.

It doesn't look like SQL Server can accommodate this. If I choose a CI collation all the tables and columns are insensitive along with the data (presumably). and the converse - If its CS everything is case-sensitive. Am I reading it right ? If so then I either have to change the collation of every text column in the DB OR edit each and every query.

Ironically the 1st test was to an Azure SQL Database which was set up with the same collation (SQL_Latin1_General_CP1_CS_AS) and it doesn't care about the case of the table name in a select.

Any ideas ?

Thanks JC

Answer 1

Firstly are you aware that collation settings exist at every level in SQL Server; Instance, database, table and even field level.

It sounds like you just want to enforce the case sensitive collation for the affected fields leaving the database and DDL as a whole case insensitive.

Another trick i've used in the past is to cast values to a VARBINARY data type if you want to do data comparisions between different cases, but without the need to change the collation of anything.

For example:

DECLARE @Var1 VARCHAR(5) 
DECLARE @Var2 VARCHAR(5)

SET @Var1 = 'Divh' 
SET @Var2 = 'divh'

--Comparison1:
IF @Var1 = @Var2
    PRINT 'Same'
ELSE
    PRINT 'Not the same'

--Comparison2:
IF CAST(@Var1 AS VARBINARY) = CAST(@Var2 AS VARBINARY)
    PRINT 'Same'
ELSE
    PRINT 'Not the same'