SQL COLLATION ISSUES

4 min readMay 9, 2023

If you’ve ever encountered the ‘Cannot resolve the collation conflict’ error message while working in SQL Server Management Studio (SSMS), you know how frustrating it can be.

Collation issues within SSMS can arise when the collation settings of the SQL Server instance and databases are misconfigured or mismatched. In this post, I will delve into the underlying causes of these collation issues and provide effective solutions to help you work more efficiently, minimizing unexpected errors along the way. Let’s start with the real error that can occur:

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

This error indicates that we are trying to compare two string columns with different collations. In this case, one column has a collation of ‘SQL_Latin_General_CP1_CI_AS’, and the other column has a collation of ‘Latin1_General_CI_AS’. Collation in general refers to the rules that dictate how characters are sorted and compared in a database, so if you try to perform operations such as sorting, searching, or comparing data that have different collations, you may encounter errors like this.

To avoid collation issues, it’s important to ensure that your SQL Server instance and your databases have the same collation settings. You can check the collation settings of your SQL Server instance by running the following query:

SELECT SERVERPROPERTY(‘Collation’)

You can also check the collation settings of your databases by right-clicking on a database in SSMS, selecting Properties, and then selecting the Options page.

In my case, collation settings in the databases that I was trying to compare were different which resulted in the error collation conflict error.

Database1:

Database2:

If you find that the collation settings are mismatched, as in my example, you can use the ALTER DATABASE command to change the collation of your database. However, changing the collation of a database can have implications for the data stored in it, so I would suggest taking a backup of the database before making any changes.

ALTER DATABASE DB_NAME COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS;

ALTER DATABASE CURRENT COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS;

After executing the command above, we can see that our column still has the old collation:

When we change the collation of the database, it will be the new default for all new tables and columns, but it doesn’t change the collation of existing objects inside the database. So, the ALTER DATABASE will affect only newly created objects in the database, and it will not change the collation type for existing columns. The collation of any existing columns in user tables will still have the original collations and if we have to change the collation for those columns we will have to change each of those columns individually through column/table properties or using the command below:

ALTER TABLE table_name ALTER COLUMN column_name VARCHAR(6) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS

The thing about collations is that although the database has its own collation, every table, and every column can have its own collation. If not specified it takes the default of its parent object, but can be different. So, if you need to have different collations on two objects or can’t change collations — you can still use the JOIN between them using COLLATE command, and choose the collation you want to join.

SELECT * FROM A JOIN B ON A.Text = B.Text COLLATE Latin1_General_CI_AS

or using default database collation:

SELECT * FROM A JOIN B ON A.Text = B.Text COLLATE DATABASE_DEFAULT

In the first example, we are explicitly specifying the collation ‘Latin1_General_CI_AS’ for both columns in the comparison, while the second command uses the COLLATE DATABASE_DEFAULT command which specifies that the column or expression should use the collation defined for the database.

The COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS command overrides the default collation of the column or expression with the specified collation. This can be useful when you need to perform operations on data that have different collations or when you need to sort or compare data in a specific way. For example, if you have a column that uses the Latin1_General_CI_AS collation and you need to compare it to a column that uses the SQL_Latin1_General_CP1_CI_AS collation, you can use the COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS command to ensure that both columns use the same collation. On the other hand, the COLLATE DATABASE_DEFAULT command can be useful when you want to ensure that all columns or expressions in a query use the same collation without having to specify it for each individual column or expression.

References:

SQL COLLATION ISSUES

Written by Biljana Jelić