Changing the tempdb Collation

The collation of tempdb cannot be changed by using the ALTER DATABASE statement—
SQL Server does not allow this since tempdb is part of the system database. Note that
tempdb uses the collation of the model database. Since there is a way to change the
collation of the model database, we inferred that we should be able to change the
collation of tempdb. Recall that the model database can be backed up and restored. So,
for example, if we have another instance of SQL Server running with a default collation
of French_CI_AS, we can back up the model database from the “French” server, restore
it on the target server, and then restart the MSSQL service on the target server. We
used this technique to change the collation of tempdb for the test cases described in
the previous section.
Note that SQL Server uses the model database as a template to create new databases.
Continuing our example, new databases would have the default collation set to
French_CI_AS.