Changing SQL Server Collation

Last week one of my team members was installing a new SQL Server 2005 instance. The installation went through fine. While validating it the Application Team figured out that the SQL Server Collation has not been set as per their requirements. The DBA had used the default collation settings.

The default collation setting can be changed for the databases easily. It can be changed in the Database Properties screen in SSMS.

Like I mentioned earlier, this option is available only for Databases. Unfortunately there is no option to change the default collation setting for SQL Server Instance. The Server Properties screen does not have an option to edit it.

Changing the SQL Server Instance’s Default Collation setting is a complex process. It can done by following the below steps.

  1. Script the users databases
  2. Export all the data in user databases
  3. Drop the user databases
  4. Rebuild the master database with the new collation setting
  5. Create the user databases (using the script generated in Step 1)
  6. Import data into the user databases

Detailed instructions on this is explained in these MSDN articles.

SQL Server 2005

SQL Server 2008

In our case, it was a new install. All the DBA had to do was to uninstall SQL Server and install it again with the correct collation setting. But having the Collation information well before installing SQL Server will save a lot of time and effort.