SQL SERVER – Case of Different Default Collation on Two Servers

SQL
5 Comments

Along with long term performance tuning engagement, I also provide quick consult (On Demand) to assist with any short-term issue, which might get fixed by talking to an expert. Some of the questions are very good and I get to learn something. This blog is the result of one such conversation like Case of Different Default Collation on Two Servers.

My client told that they are seeing that the default collation provided by SQL 2012 installation is different on 2 different servers. I always thought that the default would always be same for setup, but I was not correct.

SQL SERVER – Default Collation of SQL Server 2008

I learned that during SQL Server installation, the default collation is determined by the windows system locale. Which means, if we install SQL on one server, which has English locale, and on another server which has French locale, the SQL Server installation will choose 2 different default collation.

Solarwinds

SQL SERVER - Case of Different Default Collation on Two Servers coll-01-800x376

On a UK machine, it would be Latin1_General_CI_AS

I asked them to check the Windows system locale on those two servers. They reported back that one is US English, and another is Singapore English. I asked them to change US English to Singapore English and restart the server. After that, the SQL installation selected the same collation on the 2 servers.

SOLUTION/CONCLUSION

There is no universal default collation. It would be dependent on the locale setting of the operating system.  Here is the documentation:

How to: Change Operating System Settings to Support Localized Versions

In short, if you want same collation to be offered by SQL setup, make sure the locale is same. We can always override it by choosing the value we want.

Have you ever faced such an issue?

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
,
Previous Post
SQL SERVER – How to Downgrade SQL Server Edition?
Next Post
SQL SERVER – How to Join a Table Valued Function with a Database Table

Related Posts

5 Comments. Leave new

  • Is it true that if you change the collation of server or database you have to unload and reload all your data ?

    Reply
  • Nice article :) Is Yesterday’s puzzle also have relation with collation too? I have faced collation conflict issue in Query. which is resolved by explicitly providing collation in query.

    Reply
  • I guess that applies to server level. We can reinstall the server with correct collation settings or just run setup file again with rebuild database option.

    Reply
  • Stephen Mangiameli
    September 20, 2016 9:44 pm

    Collation affects data, database, and server. The default just states what each will get unless otherwise specified. So choosing and keeping them all aligned is incredibly important because changing collation after the fact is very difficult.

    Reply
  • Good day, is it possible to change the collation on a DB without actually stopping it (System is live and can’t be stopped)?

    Reply

Leave a Reply

Menu