If a sql server's server collation is case sensitive and a database is case-insensitive, will queries be case-sensitive or not?

Question

If a sql server's server collation is case sensitive and a database is case-insensitive, will queries be case-sensitive or not? I thought that it was supposed to be based on the database rather than the server collation, but having done a short test that seems not to be the case. Anyone know for sure?

Answer 1

There's various levels at which the collation can be specified - it could be set at the column level too which would be the one that takes precedence. Usually, a column's collation is set to use the database default, which unless specified explicitly when you create the db, will default to use the server's collation.

Note that if you create a db with one collation, and then change the collation of the db, it doesn't affect existing columns that have been defined with other collations. It would only take effect for columns created from that point on for that database. So you'd need to alter the existing columns.

What test have you done?

Answer 2

Well I guess this explains it:

The collation of an identifier depends on the level at which it is defined. Identifiers of instance-level objects, such as logins and database names, are assigned the default collation of the instance. Identifiers of objects within a database, such as tables, views, and column names, are assigned the default collation of the database. Variables, GOTO labels, temporary stored procedures, and temporary tables can be created when the connection context is associated with one database and then referenced when the context has been switched to another database. Therefore, the identifiers for variables, GOTO labels, and temporary tables are in the default collation of the instance.

Which means variables, possibly including parameters, are case-sensitive even in a case-insensitive collation. That's what I'm seeing in my testing: problems with the case of parameters but no problems with the case of table/column names.