3

We are using fresh SQL Server 2017 CU23 Standard Edition database with "SQL_Latin1_General_CP1_CI_AS". Now, I want to change all system databases (master , model , temp, msdb) to another collation. how to change this without installing SQL Server ?

thanks,

3 Answers 3

5

The only way to change the collation for the instance itself (instance-level and system DBs plus user databases) without reinstalling is an undocumented command/option. The "rebuilding system databases" option referred to in the other answers is effectively reinstalling, even if it isn't in a literal sense, nor does that approach update any user databases to match the new collation.

I posted a detailed analysis of this undocumented approach, and the implications of making any collation changes to an instance and/or database, here:

Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?

There are quite a few areas that could be affected, and it depends greatly on if you are talking about just one particular database, or the instance and one or more of the databases on that instance, and if you have any VARCHAR data containing characters with values of 128 - 255, and if you are switching sensitivities (e.g. case-sensitive to case-insensitive, or vice-verse).

With regards to the ALTER DATABASE ... COLLATE ... option:

  1. That does not work on system databases.
  2. That is not permitted if you have one of many dependencies (e.g. TVFs returning string columns, computed column returning string types, etc).
  3. For user databases where it is permitted, it only changes the system tables (e.g. sys.objects, sys.schemas, etc); it does not change any user tables (columns or indexes)
5
  • I didn't know that dependencies like TVFs & computed columns prevented the ability to change the database collation, good to know! Since changing database collations isn't something I commonly do (especially for the system databases), do you know if it is possible to set the model database collation to something other than the server's collation, and then is what I said true such that any new user databases will take the collation of the model database as opposed to the server's collation? I made a slight assumption based on what I know about model.
    – J.D.
    Apr 5, 2021 at 1:08
  • @J.D. For multi-statement TVFs the columns need to have an explicit collation defined in the table declaration of the RETURNS clause, then it will be ok. I think for inline TVFs, expressions and literals are fine if given an explicit collation via COLLATE keyword, else they will likely block a DB collation change. I thought there was a way to get computed columns to not block a DB collation change, but I've been testing again and even explicit COLLATE keyword doesn't help. And, trying to change model results in: Cannot alter the database 'model' because it is a system database. Apr 5, 2021 at 7:09
  • Gotcha, thanks! So the system databases' collations must always match the server collation, there's no way around that?
    – J.D.
    Apr 5, 2021 at 12:22
  • @J.D. There are ways around it, but you don't really want to do that as it can have unpleasant consequences, at least for master, msdb, and tempdb. I'm not sure if there is any immediate, functional downside to having model be different, but I'm also not sure why one would want all new DBs to be created in a collation that is different than the instance-level collation. If a particular collation is important enough for all new DBs, then shouldn't the instance also be using it? Either way, if you want to test, try restoring / attaching a DB from an instance with diff collation. Apr 5, 2021 at 14:02
  • oh I completely agree, I'm not sure why one would want to do such a thing either, but there's definitely an end user out there for every wacky use case one can think of lol. Rather I was just curious if my assumption about that case with how modelworks is correct.
    – J.D.
    Apr 5, 2021 at 15:37
1

The Microsoft Books Online for Set or Change the Database Collation explain how to do so, specifically how to in SSMS in the section Using SQL Server Management Studio. Alternatively you can script it as well like so:

ALTER DATABASE DatabaseName  
COLLATE French_CI_AS;

If neither of these options work for system databases, then it's not possible to do so without running the installer or rebuilding the system databases.

Either way, I would advise being careful with changing the collation of the system databases as you can run into a number of issues if not done properly. For example, a case sensitive collation that needs to interact with a case insensitive context can clash and produce errors. Also using the COLLATE function to compare between two different collation contexts can result in cardinality estimate issues.

Also, please note that if you set the model database to a collation different from the server's collation then all newly created databases will take the same collation as the model database and ignore the server's collation.

0

You can change the instance and system database collation with the setup rebuild database action. This will recreate the system databases with the specified collation without re-installing the binaries. The system databases after the rebuild will essentially be a fresh install so any changes to system objects (logins, jobs, etc.) will be lost.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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