Today, I like to provide one suggestion related to change database collation.
In Alter Database T-SQL at http://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.