Serge's Technology View

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