2

I changed the collation of the database. All of the text/varchar columns before the change were set to database default. When the change was made to the DB collation, I would have expected the columns that were set to database default to remain database default and therefore remain linked to the new DB collation. However, I noticed that these columns were actually changed to be specifically set to the old collation.

I do have a script that will update all the columns, but I'm intrigued as to why these columns didn't remain database_default or at the least get set to the new collation. So the questions I have are:

1: Why did the columns not get updated?

2: Is there a way to get them to automatically update?

Andy Lester
  • 91,102
  • 13
  • 100
  • 152
Madeleine
  • 2,152
  • 2
  • 24
  • 35

1 Answers1

3

I recently came across this problem myself. Changing the database default collation only works for new objects that are created - it doesn't change any existing ones (source: Books Online - "You can change the collation of any new objects that are created in a user database by using the COLLATE clause of the ALTER DATABASE statement. This statement does not change the collation of the columns in any existing user-defined tables. These can be changed by using the COLLATE clause of ALTER TABLE."

I'm not aware of any way to make them update automatically - you need to update them manually. I used the method from here

Jon
  • 16,212
  • 8
  • 50
  • 62
  • Thanks for the link - so I'm not going crazy seeing that the table columns aren't being updated! Seems a bit unfortunate that the option is not given by MS to update all columns. This line answers my question: "The ALTER DATABASE COLLATE command will only change the *DEFAULT* collation of the database, it will not change collation of existing objects (e.g. columns & tables). They will still be stored in the old collation order even if the default is changed." Thanks! – Madeleine May 05 '09 at 16:51