Recently, one of the project teams at the office had a customer-specified requirement to use a SQL Server running a server collation other than the English default (SQL_Latin1_General_CP1_CI_AS).
Please NOTE: Collation is a vast topic. The scope of this post is only present the means to choose the server collation at the time of installation or afterwards.
We have two major variants of the English language supported by Windows:
- “English (United Kingdom)”, Windows LCID = 2057 (Hex: 0x809)
- “English (United States)” , Windows LCID = 1033 (Hex: 0x409)
Each Windows LCID maps to a SQL Server side’s SQL LCID. Per the article “Collation Settings in Setup”, both 2057 and 1033 map to a single collation – 1033 (0x409), which is SQL_Latin1_General_CP1_CI_AS.
Unfortunately, the IT team who setup the servers for them had used the default values during the installation. When the issue was realized, they tried to change the server collation by changing the collation of the master database (because the collation of the SQL Server instance is that collation of the master database), which returned the following error:
Cannot alter the database ‘master’ because it is a system database.
Workaround (Reactive)
When their attempts failed, they called me and my instant reason was that because this is a customer-specified requirement, they would have to do the following:
- Backup and drop all user databases
- Rebuild the master database by specifying the new collation name in the SQLCOLLATION property of the SQL Server setup (command-line) as shown in the Books On Line here
- Re-create all user databases
Ideal solution (Proactive)
The ideal solution here would be to setup the SQL Server such that it uses the required collation during installation itself. The Server Configuration page (step #12) in the SQL Server setup allows the administrator to specify the collation that SQL Server would use:
By default, the SQL Server setup chooses a collation that matches to the operating system settings. However, the administrator can use the “Customize” button to choose a collation of their choice.
References/Further Reading:
- Using SQL Server Collations: http://msdn.microsoft.com/en-us/library/ms144260.aspx
- Collation Settings in Setup: http://msdn.microsoft.com/en-us/library/ms143508.aspx
- Setting and changing the Server Collation: http://msdn.microsoft.com/en-in/library/ms179254(v=sql.110).aspx
- Collation & Unicode support: http://msdn.microsoft.com/en-us/library/ms143503.aspx
Until we meet next time,
Be courteous. Drive responsibly.
SQLTwins by Nakul Vachhrajani 