Does sqlserver collation mean column names must be correct case? And how to deal with that...

Question

In SQL Server (2000 or 2005) is it possible to set the database or server collation so that identifier names (tables, columns, etc) need to be in the correct case? If so, is that true of all case-sensitive collations or is it a separate setting? (I've always thought of case-sensitivity applying to data, not to names of objects).

Presumably this would break an application if its stored procs and queries weren't written with consistent case? Is there a way to deal with this without having to ensure all queries use the correct case, such as setting the collation of a database connection?

I'm looking at this from the point of view of having an existing application which probably has inconsistently cased sql code in it, and I'm wanting to be able to run it against databases with different collations. What settings would I need or what set of database/server collations could I not use the application with?

Answer 1

You can set collation for each object, and set a default for the database and server as well.

How to deal with it? You need to enforce standards here. You can easily get yourself tangled up with different people write with different case.

The collation also applies to data so "bob" != "Bob"

Answer 2

The collation is what determines if your queries will be case insensitive. So the only way to ensure that your schema will work against multiple environments is to have your queries be case sensitive. If your queries are not consistent, then your collation MUST be case insensitive otherwise it will not work.

http://msdn.microsoft.com/en-us/library/aa174903(SQL.80).aspx

One thing to note is that once you've set up your SQL Server environment with a certain collation, you CANNOT change it without creating a NEW SQL Server instance. So Case-Insensitive is usually the way to go. And then strive to have consistency in your queries.

Once a collation is set it applies to both data and metadata, I believe.

Answer 3

Collation is set in earlier versions of SQL Server, but in 2005 and beyond, you can change it by object, as they are created.

Answer 4

The database default collation determines whether objects within the database are treated in a case-sensitive way in queries - this applies to all object name: tables, columns, etc.

If your application code comes from a case-insensitive collation database, it may not run on a case-sensitive collation database if a object is misreferenced (you would get a message when you attempted to run the statement or create the stored procedure, or in a stored-proc architecture, you would catch all these pretty quickly unless you had a significant amount of dynamic SQL).

Remember, that even if your code runs, individual columns can be set with collations which differ from the database, so it's always possible that with a differing collation, your code will behave unexpectedly (for instance, GROUP BY behaves differently).