As a SQL Server developer, you may have encountered error messages that seem confusing and difficult to understand. One such error is the “Cannot resolve the collation conflict” error. In this blog post, we will explore this error and learn how to resolve it.

Collation refers to the rules that determine how string comparison is performed in SQL Server. Each database, table, and column in SQL Server has a collation setting. When you try to compare or join columns with different collations, you may encounter a collation conflict error.

Let’s take a look at an example to understand this error better. Consider the following scenario:

A developer is working on a project for a European client and encounters the following error message:

Msg 468, Level 16, State 9, Line 15
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "French_CS_AI" in the equal to operation.

The developer wants to understand why this error occurred and how to reproduce it in their environment. They mention that they were using temporary tables when the error surfaced.

To help the developer, we can create a simple script to mimic this error. First, let’s check the collation of the server:

-- Server Collation
SELECT SERVERPROPERTY('Collation')

In this case, the server is using the default collation “SQL_Latin1_General_CP1_CI_AS”.

Next, let’s create a database with a different collation, such as “French_CS_AI”. We will also create two tables, one regular table and one temporary table:

-- Create database with different collation
CREATE DATABASE [DB_Not_Contained]
CONTAINMENT = NONE COLLATE French_CS_AI

-- Create 2 tables with 1 being a temp table so it goes to tempdb and uses the server collation and not the database collation
USE [DB_Not_Contained]
GO

CREATE TABLE [DemoCollation] ( DemoCollationNM VARCHAR ( 100 ))
GO

CREATE TABLE #DemoCollation ( DemoCollationNM VARCHAR ( 100 ))

-- Insert records into both tables
INSERT dbo.DemoCollation ( DemoCollationNM ) VALUES ( 'Test Join' );
INSERT #DemoCollation ( DemoCollationNM ) VALUES ( 'Test Join' );

Now that we have set up the data, let’s try to join these two tables:

-- Now query and try to join both tables having 2 different collations
SELECT p.DemoCollationNM
FROM dbo.DemoCollation p
INNER JOIN #DemoCollation d ON p.DemoCollationNM = d.DemoCollationNM

When we execute this query, we will receive the collation conflict error:

Msg 468, Level 16, State 9, Line 35
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "French_CS_AI" in the equal to operation.

So, what can we learn from this example?

First, we cannot join tables or columns when there is a conflict in collation between the objects. In our case, the regular table and the temporary table had different collations, leading to the error.

Second, temporary tables in SQL Server use the same collation as the server by default. This means that any objects created within a temporary table will also have the same collation as the server.

To resolve this collation conflict, we can either change the collation of the objects involved in the query or use the COLLATE clause to explicitly specify the collation for comparison.

Have you ever encountered this error in your SQL Server projects? How did you resolve it? Share your experiences in the comments below!