Changing the collation of the tempdb database
Posted by decipherinfosys on September 2, 2008
We cannot use the “ALTER DATABASE” command to change the collation of the tempdb database. But there is a way to make this change – tempdb get’s it’s collation from the setting for the model database on the instance. So, if we can somehow change the collation of the model database and re-start the service, tempdb should inherit that collation. Let’s try that out:
select databasepropertyex (‘model’, ‘collation’)
select databasepropertyex (‘tempdb’, ‘collation’)
Now, let’s change the collation of the model database. Since model is a system database, one cannot change the collation of this database by using the ALTER database command – however, the model database can be backed up and restored. So, if we have another instance where the default collation is say “Chinese_PRC_CI_AI”, then we can backup and restore from that instance to our instance and re-start the service in order to get the collation that we want for tempdb on our instance as well.
This would rarely be a need in real life scenario since if you want a particular collation for tempdb, you would choose the right collation to begin with for the instance when you are doing the install. However, in our case, we were doing some benchmarks for a client of ours and this requirement was raised pretty late in the game and the above method worked out great for us. In real life scenario, this would have been a bit more cumbersome since the model database could have other settings/objects etc. which would then need to be re-created after the restore.