SQL Server Performance Forum – Threads Archive

Implications of using the wrong collation setting?

When our servers were built the regional settings were left as US even though we are in the UK. SQL 2000 was then installed using the defaults and so the codepage is set to SQL_Latin1_General_Cp1_CI_AS Not realising that the locale being used in the company was US I built another server and set the default locale to UK and then installed SQL server 2000 which resulted in SQL having a different collation. When I resotred some databases to my new server there were problems with some of the views and it was traced back to the collation settings. Looking forward, should the company resolve the fact that we have an incorrect collation on our SQL servers? The implication of this is that all SQL servers will have to be built with US regional settings – otherwise our databases will not function. Is there a compelling reason why we should resolve this – or just stick with it? What are the implications of sticking or changing?

The SQL server setup will look at the regional setting on your servers and default to that collation in SQL server as well if you do not change it during setup. If possible keep the collation settings the same. Because even if you can change collation on database level (and have databases with different collation on same server) the system databases can only have one collation. So if your application does for example string comparison in tempDB you can run into trouble unless you specifically code around this on the application. Older applications most likely will not have the code implemented to make them work when multiple collations are used (again tempDB as example). But if you have servers that have nothing to do with eachother, then having different SQL collation setting won’t matter. But if you at any time need to restore databases from one server to another it helps a lot if their SQL default collation is the same. Also you do not need to build the servers with same regional setting on operating system level, you can always change the sql server collation during installation, but it helps to keep this is sync as well, especially if you have applications on the servers as well that depend on this. But is not needed to keep them in sync because of SQL Server.

We do face similar issue on few of our services but not in terms for major issue, though it refers windows locale as English-US and there was no service outage issues. Then coming to the collations on SQL, as suggested try to keep similar values on all involved servers. From SQL 2K its an advantage to maintain collation on database/table level and I think there will not be any issues with different windows locale.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>