SQL Server Collation And Case Insensitive Identifiers

0
votes

We have an old SQL Server database with case sensitive collation, but our newer application code expects column and table names to be case insensitive.

So, we are trying to convert the database so that the column and table names will be case insensitive.

The solution so far is to create an empty DB with case insensitive collation, then generating scripts from the old database to recreate the schema in the new database (using SQL Server Studio), then exporting the data from the old database into the new database.

This almost works, but the old instance allowed for a couple of rows of data that are identical when ignoring case sensitivity and we have a unique constraint violation.

Is there a simple way to allow for SQL references to table and column names to be insensitive while column data is treated as sensitive still (without having to modify the columns individually with separate collations)?

1
sqlsql-servercollation
Just to make sure -- have you considered convincing the application guys to make their code case-clean? It might be painful, but it really ought to work with a database that chooses to use a case-sensitive collation, exactly to avoid gruntwork like this. – Jeroen Mostert
@JeroenMostert That will be the long term solution, but we need to resolve this quickly in the short term. – sixbitproxywax
I changed my answer where I said "no", because on further reflection I think the actual answer is "yes". :-) – Jeroen Mostert
@JeroenMostert Yes, I think that's what we want. I need to check the application code to make sure this will be ok, but I think I want to have CI identifiers and CS column data (like Oracle?) – sixbitproxywax
@JeroenMostert Done! – sixbitproxywax

1 Answers

1
votes

When you create a database, the system tables that hold the object names get the collation of the database -- here you want an insensitive collation. If you then want all your data to be case sensitive still, change the database's default collation, then run your scripts.

Changing the collation will not change the collation of already created objects (including the system tables) but it will affect subsequent objects.

Without changing the column collation at all, you can override it on individual queries, for example WHERE A = 'Hi' COLLATE Latin1_General_CS_AI, but this is inefficient because it will prevent the use of indexes for seeking (as conversions are required). It's not possible to define a constraint with a collation override -- you'd have to get complicated and define it on a computed column with a separate collation.