Changing database collation doesnt update collation of varchar/text fields in the DB (MS SQL)

Question

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?

Cloister

Answer 1

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

Affectation

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! – Cloister

The link is now dead, so the last sentence completely lost its value. – Chalfant