How to define SQL Server colum name case insensitive but values case sensitive

Question

We just migrated some databases to a new SQL Server 2012 and got some problems with sensitivity.

We would like table & column names to be case insensitive but values should be case sensitive, so

select ... where 'a'='A'

should not return any row, but

select Column from Table
select column from table

should both work.

We tried changing the database (server default) from Modern_Spanish_CI_AS -> 'a'='A' is true, which we don't want to be, to Modern_Spanish_CS_AS -> the column/table names must match the defined case

Is there any way to get the desired behavior?

Answer 1

If you choose a case-sensitive collation you must ensure that your your queries are case-sensitive because collation applies to metadata as well as user-data .

You can get round the problem by making the database's collation case-insensitive and using the COLLATE clause when creating tables, or alternatively use a contained database.

Read more about Contained Databases and Contained Database Collations