When installing SQL Server, you may have noticed that the default collation provided by the installation can vary depending on the server. This can be confusing, especially if you expect the default collation to be the same across all installations. In this blog post, we will explore the concept of default collation in SQL Server and understand why it can differ between servers.

What is Collation?

Collation refers to the set of rules that determine how string comparison and sorting operations are performed in a database. It defines the character set, case sensitivity, and accent sensitivity for the data stored in the database.

Default Collation and Windows System Locale

During the SQL Server installation process, the default collation is determined by the Windows system locale. The system locale represents the language and regional settings of the operating system. Therefore, if you install SQL Server on a server with an English locale, the default collation will be different from a server with a French locale.

For example, on a UK machine with an English locale, the default collation would be “Latin1_General_CI_AS”. However, if you install SQL Server on a server with a different locale, such as Singapore English, the default collation will be different.

Case Study: Different Default Collation on Two Servers

Let’s consider a scenario where a client reported that they were seeing different default collations on two SQL Server instances. One server had a US English locale, while the other had a Singapore English locale. This resulted in different default collations being selected during the installation process.

To resolve this issue, we advised the client to change the US English locale to Singapore English on the server with the mismatched collation. After restarting the server, the SQL Server installation automatically selected the same collation on both servers.

Conclusion

It is important to note that there is no universal default collation in SQL Server. The default collation is determined by the Windows system locale during the installation process. If you want the same collation to be offered by the SQL Server setup, ensure that the locale settings are the same on all servers.

However, it is possible to override the default collation by manually selecting a different collation during the installation process.

Have you ever faced a similar issue with default collation in SQL Server? Share your experiences in the comments below!