The Pains of Collation Settings

Collation settings define the physical storage of character strings in SQL Server. It specifies the bit patterns that represent each character and the rules by which characters are sorted and compared. Collation settings have a direct impact on the databases if you configure them to use a different collation other than the default. When you configure a different collation setting during the installation, the rest of the databases (system and user-defined) will have this setting was their default when you create them unless you specifically defined something different from the default. One case I encountered was that the tempdb database had a different collation setting compared to the user databases. This is because SQL Server was reinstalled and the master and msdb databases were restored from backup together with the user-defined databases. This made the collation settings of the entire database server back to their original configuration - well, almost. Since the model database was not restored (and obviously, the tempdb database does not require a backup), collation settings for the both would be the settings defined during re-installation. A script executed in one of the user-databases would require the use of the tempdb and since they do not have the same collation settings, queries will fail unless the queries themselves take into account collation setting differences. The next best bet is to rebuild the master database and change the collation settings and go through all the grueling task of restoring the system databases, provided that the user-defined databases are still intact. Well, another approach would be to simply restore the model database if you have a backup (or simply look for the old MDF and LDF files if they are still working fine). This is because tempdb database takes the settings of the model database everytime SQL Server service starts. This will resolve collation problems should you encounter a similar case