Do not alter database collation in your server

Today, I like to provide one suggestion related to change database collation.

In Alter Database T-SQL at https://msdn.microsoft.com/en-us/library/ms174269.aspx,  we can see example of changing database 's collation as:

  Alter Database db2 collate latin1_general_ci_as

It seems pretty trival to change a database collation.  However,  it actually does not work in most of the cases.  The root reason is that the above statement NEVER change your existing tables' collation, but it only change the collation of store procedure and use defined function's parameter, and the system meta data. 

So this statement will leave your database in a bad state: part of objects have old collation, and part of objects have new collation, and newly created objects will have new collation.  As a result, you may start to seeing that your application start to failing.

 

So,  I suggest is that NEVERY use ALTER Database ALTER Collate to change a collation.  The best way so far is that create a new database, and migrate data into the new database. 

In addtion, I am working on a tool to help people to do this automaticly, and it does not create any database.  Please let me know if you have any suggestion.

Comments

• Anonymous
  April 04, 2011
  here you can find a way to change the collation of the database (without data migration) bytes.com/.../634037-changing-collation-types-indexes-love-them

• Anonymous
  February 27, 2012
  Thanks a lot for QingsongYao for your warning advice. And thank you so much for Sylvio.Hellman for your shared solution link, I think. :D