Is SQL Server Collation setting your enemy or your friend? How to change server default collation?

In general, it is your friend, it will help SQL Server to figure out how to store character strings in different locale. It becomes even more important when data you are storing goes beyond regular ANSI charset (Chinese, Japanese, etc.).

In my case I, from time to time work with Cyrillic. Being born and raised in Russia, I am native Russian speaker. As a result I am keeping Cyrillic enabled on my machine as main locale setting.

It is OK in many instances, but when it comes to SQL Server or some other applications which are trying to be smart in this area, it becomes a problem.

If you install SQL Server instance in such environment, you would get something like that

Let check collation for some database I have

SELECT databasepropertyex(db_name(),'collation') AS collation_name

In my case result would be

 Cyrillic_General_CI_AS

If you are at home, it may be fine, but at work when your primary locale is SQL_Latin1_General_CP1_CI_AS
it could be a problem since you often will run into error when execution of the SQL code across different databases will return Collation error.

Too late? Not exactly.

In many cases your solution would be to adjust Collation setting for the database using to following code:

ALTER DATABASE DatabaseName COLLATE SQL_Latin1_General_CP1_CI_AS

Problem would still exist though, any new database created will get default Collation setting from … MASTER database for your SQL Server Instance.

 And here some hoops will be required. Following steps will help you overcome the issue:

  1. Detach all your user databases
  2. Stop your SQL Server
  3. In C:\Program Files\Microsoft SQL Server\80\Tools\Binn\ folder locate rebuildm.exe and run it. This utility which comes with SQL Server (you will need to have client tools to be installed) allows you to repair your master database from original installation CD/DVD/Folder and also change default Collation Setting for your SQL Server instance. Go to Settings and choose appropriate collation designator or SQL Collation.
    If you cannot find rebuildm.exe, it means you are using SQL Server 2005 or 2008. Then the following may help:
    for SQL Server 2005

    start /wait <CD or DVD Drive>\setup.exe /qn INSTANCENAME=<InstanceName> REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=<NewStrongPassword>

    for SQL Server 2008

    setup.exe  /QUIET  /ACTION=REBUILDDATABASE  /INSTANCENAME=instance_name  /SQLSYSADMINACCOUNTS= accounts   [/SAPWD=password]
    
  4. Rebuild your master database
  5. Reattach your user databases
  6. Check that collation for master and user databases is a desired using first query above.

This is much easier then to recreate a SQL Server instance from scratch.

Note. There is less safe way of getting the same result – adjust Windows Registry.
Your default collation name is stored under

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\
DefaultCollationName

6 Comments

Steve Allen · May 2, 2009 at 13:30

I understand rebuildm.exe is not supported in sql 2005. Is there an alternative?

Serguei Dosyukov · May 2, 2009 at 14:02

I have extended the post to add 2005 and 2008 alternatives

Deanna McMurray · Sep 15, 2009 at 13:38

Hello, I also need to change the collation for a SQL Server 2008 server. So it is true that by detaching a user db, then rebuilding master as directeed about (and as part of rebuilding master resetting a new server collation) and then once this is complete then reattaching the user database will then change the user database’s collation to match the new server collation? Can you verify this? I had seen on some SQL 2000 documentation that an attached database will retain the collation of the original source database–this seems to imply this has changed in SQL 2005/2008–that it somehow is set to the new server collation level. If you could verify you have done this successfully I would appreciate it.
Thanks.

Serguei Dosyukov · Sep 15, 2009 at 13:52

Per discussion here and here, method does work for 2008. There you also can find some additional params.

Deanna McMurray · Sep 15, 2009 at 14:10

I am still a bit confused–the post that you refer to on social.mdsn.microsoft.com seems to imply you need to still reload all of your user data from a db with the old collation to one with the new collation–this is what I wish to avoid. Do you have any personal experience with seeing if a reattached database will pick up the new collation of a server?

Serguei Dosyukov · Sep 15, 2009 at 15:28

Where do you see it? You confuse two operations:

* adjustment of the collation of the user database – this is done by the ALTER DATABASE statement. And yes, it works for 2000, 2005 and 2008. It may be good idea to mention that it is very useful operation when you restore database from the server which has different colation then the target to bring it in sync.
* adjustment of the collation of the master database – this is done using rebuild process mentioned in second part of the post.

These operations are not linked together per se. First do just that – modifies user database collation. But without second, any NEW database would have collation inherited from master database.

Leave a Reply