Sql-server – Change the server Collation

collationsql server

I am discussing a controversial subject on the server Collation.

When I prepare the 70-462 exam I learned that if we want to change the default collation of the new coming databases we have to change the collation property in the 'Model' database.

However on the internet all articles said that The collation of system databases cannot be changed. And I validate this when I tried to execute the following query:

Alter DATABASE model COLLATE SQL_Latin1_General_CP1_CI_AS

I get an error telling that it is impossible to change the collation of the database Model because it is a system database.

Is it true? and if yes, is there any way to change the default collation of the server without doing a reinstallation and by t-sql code?

Thanks

Best Answer

Technically yes, you can change the Instance's default Collation without reinstalling by "rebuilding the system databases" via the SETUP program. This is documented in the following two places on MSDN:

However, as can be inferred from the instructions on those two pages, this is not exactly a simple / low-risk process. And, this only changes the 4 system databases, not any user databases.

There is also the undocumented -q option for sqlservr.exe as linked to in @irimias's answer (please see Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong? for a detailed description of the behavior of this option, as well as the potential scope of impact). This option changes all databases (system and user), but is undocumented, so not guaranteed to work 100% (but it might).

Now, while you did mention the default Collation for new databases, one should still keep in mind that it is easy enough to specify the desired Collation via the COLLATE clause as follows:

CREATE DATABASE [NewDatabaseName] COLLATE Hebrew_100_CI_AS;

Still, that is not 100% the same as the Instance being the same Collation as the Database since some columns of various DMVs, plus tempdbs and msdbs default Collation will still be the same as the Instance default, which increases the chances of getting Collation-mismatch errors. But, those can still be worked-around using the COLLATE clause on any predicates or JOIN conditions on strings fields that get those mismatch errors.

Related Question