Introduction

Have you ever wondered if the collation of your user databases and system databases in SQL Server could impact performance? As a database administrator, I’ve grappled with this question myself. In this article, I’ll share what I’ve learned about collations, how they affect queries, and whether it’s a good idea to change system database collations to match user databases. By the end, you’ll have a clear understanding of this important but often overlooked aspect of SQL Server configuration.

What is Collation?

First, let’s define what collation means in SQL Server. Collation refers to a set of rules that determine how character data is sorted and compared. It specifies things like:

• Case sensitivity (A vs a)
• Accent sensitivity (o vs ó)
• Kana sensitivity (Japanese characters)
• Width sensitivity (single-byte vs double-byte)

SQL Server supports a wide variety of collations to handle different languages and alphabets. You can see the full list in the sys.fn_helpcollations system function.

Default Collations

When you install a new instance of SQL Server, the setup program assigns default collations at two levels:

1. Server collation – applies to system databases (master, model, msdb, tempdb)
2. Database collation – applies to newly created user databases

Typically the server and database collations are set to the same value, based on the OS language. For example, a US English installation would default to SQL_Latin1_General_CP1_CI_AS.

You can override the database collation when creating a new database:

CREATE DATABASE MyDB 
COLLATE French_CI_AS;

This allows you to support multiple languages within the same instance.

Collation Conflicts

So what happens if your user database collation doesn’t match the system databases? In most cases, it’s not a problem. Queries against the user database will use its collation setting.

However, you may run into collation conflict errors in certain scenarios, such as:

• Joining tables from databases with different collations
• Comparing columns to literal string values
• Using temp tables or table variables

For example:

SELECT *
FROM UserDB.dbo.Table1 t1
JOIN tempdb.dbo.#TempTable t2 
  ON t1.Col1 = t2.Col1;

If the collations differ between the user table and the temp table, SQL Server will raise an error:

Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “French_CI_AS” in the equal to operation.

To avoid these errors, you have two options:

1. Explicitly convert the collation using the COLLATE keyword:

ON t1.Col1 = t2.Col1 COLLATE DATABASE_DEFAULT

2. Create the temp table with the same collation as the user database:

CREATE TABLE #TempTable (
  Col1 NVARCHAR(10) COLLATE French_CI_AS
)

Performance Impact

Now let’s address the main question – does having mismatched collations between user and system databases negatively impact performance?

The short answer is no. SQL Server handles collation conversions very efficiently under the hood. In most workloads, you won’t notice any significant difference in query speed.

That said, there are a few edge cases where collation mismatches could result in slower queries:

• Queries that do a lot of string comparisons across databases
• Queries that use temp tables extensively
• Queries that involve sorting or grouping on character columns

In these scenarios, the extra collation conversions could add a small amount of CPU overhead. But we’re talking about very minor differences, likely not even measurable in real-world usage.

Changing System Collations

So if you’re experiencing collation conflicts, should you change your system database collations to match the user databases? I would advise against it, for several reasons:

1. It requires rebuilding the system databases, which is a complex and risky operation. You’ll need to schedule downtime and have a solid backup/recovery plan.
2. It may break other applications that expect the default collation. You’d need to thoroughly test everything.
3. It provides little to no tangible benefit, since collation differences have minimal impact on performance.
4. It makes your instance non-standard, which can cause confusion for future DBAs.

In almost all cases, it’s better to leave the system collations as is and work around any collation conflicts in the application code or database objects. Save yourself the headache!

Conclusion

To recap, while collation differences between user and system databases in SQL Server can cause some query errors, they don’t materially affect performance in the vast majority of cases. Changing system collations is rarely necessary or advisable.

I hope this article has clarified this often misunderstood topic. The key takeaway is to be mindful of potential collation conflicts and know how to resolve them with COLLATE or temp table DDL. But don’t lose sleep over having mixed collations in your databases – it’s usually not worth the pain of “fixing”!

For more details on collations in SQL Server, check out the official Microsoft documentation: Collation and Unicode support

Feel free to leave a comment if you have any other questions! Happy querying!