SQL Server: impact of column collation on T-SQL instructions

Question

I discovered(*) today that, depending on the server, my TSQL commands were case-sensitive, meaning that, when one table's column is named tableId, the following instruction might not succeed:

SELECT TableId FROM myTable

Depending on the column's collation. SQL_Latin1_blablabla seems not to be case-sensitive, when Latin1_blablabla is.

So my first question is WHY!!!

And the second one is: what is the quickest trick (sp?) to change all collations for all concerned columns in the database?

EDIT: to make things very clear:

SELECT tableId FROM myTable

Work on all servers while

SELECT TableId FROM myTable

Works only on the server with the SQL_Latin_blablabla collation. Notice the difference between the 2 strings. We are not talking here about data collation, but about the impact of this collation on the way we write code!

(*) I could use here some additional and specific word to qualify my state of mind after this 'discovery', but all of them are adult-rated ...

Answer 1

This is called a collation and it controls whether SQL Server treats your strings as case sensitive and/or as accent-sensitive (e.g. if it will recognize è é à and so on, or treat them the same as e a and so on)

This is a feature - not a bug!

You can find your current collation with this command:

SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation') SQLCollation;

You'll get something like:

Latin1_General_CI_AS

The "CI" means: Case Insensitive - CS would signify Case Sensitive

The "AS" means: Accent Sensitive (and AI would be accent insensitive)

You can define a standard collation for your SQL Server at the time you install it, and you can override that for each database you create.

You can also easily create tables and columns and specify specific collations for each VARCHAR field.

To change the collation for the database, you can use this command:

ALTER DATABASE MyDatabase COLLATE <desired collation>

Unfortunately, there are lots of reasons why this command might fail.... anything that's "schema-bound" will prevent the collation change. In that case, you can only drop or disable all those "roadblocks" and then try again - quite an ordeal!

If you choose e.g. "Latin1_General_CI_AS", you should no longer have case-sensitive table and column names.

If you want to change the collation of a single column in a datatable, use this command:

ALTER TABLE Table1
  ALTER COLUMN Column1 VARCHAR(20)
    COLLATE Latin1_General_CS_AS  -- or whatever collation you want

Marc

Answer 2

Following on from marc_s

sys.objects, sys.columns etc store the column names and object names in the collation of the database. So, a binary collation means object names treated as binary...