Scenario

Today my customer told me that he is facing an error message ‘Cannot resolve the collation conflict’ when executing an JOIN between two tables: a base table and a temporary table. They had recently created the database afresh from scripts and were facing this issue since. On the existing ‘good’ server the problem did not occur.

The only obvious difference was that the ‘good’ database and ‘bad’ database differed in collations.

It is a fairly common problem and one which has a simple solution, but it does need to be handled in T-SQL code to ensure that it works in all possible scenarios. Here is a simple reproduction of the issue.

Walkthrough

Firstly, let us check the SQL instance collation:

select SERVERPROPERTY(‘Collation’)
go

Case 1: Temporary tables without any specific column level collation specifier

It tells us that the instance is at the default: SQL_Latin1_General_CP1_CI_AS. Next, let us simulate the ‘bad’ database, which had a different collation from the instance collation, and create a table in that database.

CREATE DATABASE Ukrainian COLLATE SQL_Ukrainian_CP1251_CS_AS
GO

USE Ukrainian
GO

CREATE TABLE MyTab
(
    someval VARCHAR(50)
)
GO

SELECT collation_name
FROM sys.columns
WHERE name = ‘someval’
and object_id = object_id(‘MyTab’)
GO

As you can see, the column inherits the database collation, namely SQL_Ukrainian_CP1251_CS_AS. Next, let us simulate the actual issue:

CREATE TABLE #something
(
    somevaltemp varchar(50)
)
GO

SELECT *
FROM MyTab JOIN #something
ON someval = somevaltemp
GO

The error is reproduced:

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

This is interesting because tables created in TEMPDB inherit the collation of TEMPDB unless the collation is explicitly specified. A quick check from sys.columns in TEMPDB will confirm it:

SELECT collation_name, name
FROM tempdb.sys.columns
WHERE name = ‘somevaltemp’
GO

The result is the same as the instance / TEMPDB collation: SQL_Latin1_General_CP1_CI_AS.

Case 2: Temporary tables with collation specified for the column

Next, let us try to create the temporary table with a collation value identical to the database from which it is created:

CREATE TABLE #somethingexplicit
(
    somevaltempexplicit varchar(50) COLLATE DATABASE_DEFAULT
)
GO

We can also confirm the collation of the newly created column:

SELECT collation_name, name
FROM tempdb.sys.columns
WHERE name = ‘somevaltempexplicit’
GO

As you will see it is now SQL_Ukrainian_CP1251_CS_AS. Let us attempt the JOIN again this time against the new temporary table:

SELECT *
FROM MyTab JOIN #somethingexplicit
ON someval = somevaltempexplicit
GO

You will see that it succeeds without any issues.

Case 3: Temporary tables generated by a SELECT…INTO

Let us try a final test, this time with a SELECT INTO a temporary table:

SELECT someval AS somenewvalinto
INTO #tempselectinto
FROM MyTab

SELECT collation_name, name
FROM tempdb.sys.columns
WHERE name = ‘somenewvalinto’
GO

As you can predict, the columns of the temporary table inherit the collation from the base table which we are selecting from. So the collation is retained as SQL_Ukrainian_CP1251_CS_AS.

SELECT *
FROM MyTab JOIN #tempselectinto
ON someval = somenewvalinto
GO

The JOIN also succeeds in the final case.

Conclusion

If you are creating a temporary table from your database code, and if your database happens to use an explicit collation, it is your DUTY to specify the collation in the CREATE TABLE call for the temporary table. You can either explicitly specify the collation value or use DATABASE_DEFAULT clause.