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

  • You'll need to reinstall SQL Server to change the instance collation (master, model, and tempdb). – Dan Guzman Feb 14 '17 at 11:51

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.

You can try to follow this procedure to change server collation.

I tested it once and it worked well.

But actually, I'm not really sure this is very orthodox and supported by Microsoft....

  • 1
    It may work (I used it in some situations) but it is not supported. Don't use this technique if you're planning to use this instance in production. – spaghettidba Feb 14 '17 at 12:17
  • 2
    @spaghettidba, and this answer is unlikely to be the answer for the exam question :-) – Dan Guzman Feb 14 '17 at 12:55
  • I also have to point out that [-q] as show in the article is not documented, subject to change, and not supported. – Sean Gallardy Feb 14 '17 at 14:59

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.