Cannot resolve the collation conflict

One possible cause of the “Cannot resolve the collation conflict” error message is that your database collation doesn’t match the TempDB Collation.

Recommendation

It is recommended that the collation of your database match the collation TempDB.   It is possible that if your collations do not match that you may get errors, or that queries using TempDB will not run correctly.
If you use temp tables and your database collation doesn’t match the TempDB collation you will get an error similar to the following error when you attempt to join to Temp Tables:

Msg 468, Level 16, State 9, Line 11
Cannot resolve the collation conflict between “SQL_Latin1_General_CP437_CI_AS” and “Latin1_General_CI_AS” in the equal to operation.

Example Code

First to try the example, you need to check the collation of TempDB.

SELECT DATABASEPROPERTYEX('tempdb', 'Collation');

Which will look something like this (actual collation may vary).

MyTempDBCollation

Then you need a database that has a different collation from that of TempDB.


CREATE DATABASE [CollationTest]
 COLLATE Latin1_General_CI_AS;

or if TempDB is Latin1_General_CI_AS then make it different from TempDB


CREATE DATABASE [CollationTest]
 COLLATE SQL_Latin1_General_CP437_CI_AS;

Once you have a database with a different collation, you need some data to work with.  In this example, I am going to use the table from my CTE presentation.


use [CollationTest];
CREATE TABLE [Departments] (
 id int, --would normally be an INT IDENTITY
 department VARCHAR (200),
 parent int
 );

-- insert top level departments
insert into [Departments] (id, department, parent)
 values (1, 'Camping', null);
insert into [Departments] (id, department, parent)
 values (2, 'Cycle', null);
insert into [Departments] (id, department, parent)
 values (3, 'Snowsports', null);
insert into [Departments] (id, department, parent)
 values (4, 'Fitness', null);

-- now some sub-departments for camping
insert into [Departments] (id, department, parent)
 values (5, 'Tents', 1);
insert into [Departments] (id, department, parent)
 values (6, 'Backpacks', 1);
insert into [Departments] (id, department, parent)
 values (7, 'Sleeping Bags', 1);
insert into [Departments] (id, department, parent)
 values (8, 'Cooking', 1);

-- now some sub-departments for cycle
insert into [Departments] (id, department, parent)
 values (9, 'Bikes', 2);
insert into [Departments] (id, department, parent)
 values (10, 'Helmets', 2);
insert into [Departments] (id, department, parent)
 values (11, 'Locks', 2);

-- now some sub-departments for snowsports
insert into [Departments] (id, department, parent)
 values (12, 'Ski', 3);
insert into [Departments] (id, department, parent)
 values (13, 'Snowboard', 3);
insert into [Departments] (id, department, parent)
 values (14, 'Snowshoe', 3);

-- now some sub-departments for fitness
insert into [Departments] (id, department, parent)
 values (15, 'Running', 4);
insert into [Departments] (id, department, parent)
 values (16, 'Swimming', 4);
insert into [Departments] (id, department, parent)
 values (17, 'Yoga', 4);

Now we check the collations to confirm that they are different.


use [CollationTest];
SELECT DATABASEPROPERTYEX('tempdb', 'Collation') as TempDBCollation,
 DATABASEPROPERTYEX('CollationTest', 'Collation') as ThisDBCollation;

Lets see where it breaks:


use [CollationTest];
CREATE TABLE #TempDepartments (
 id int,
 department VARCHAR (200),
 parent int
 );

INSERT INTO #TempDepartments
SELECT TOP 5 * FROM [Departments];

SELECT *
  FROM [Departments] d
 INNER JOIN #TempDepartments td
    ON d.department = td.department;

DROP TABLE #TempDepartments;

Which will produce the following error:

Msg 468, Level 16, State 9, Line 11
Cannot resolve the collation conflict between “SQL_Latin1_General_CP437_CI_AS” and “Latin1_General_CI_AS” in the equal to operation.

 How do we fix it?

You could change your database collation to match what TempDB is using, or you could specify the collation in the query.

use [CollationTest];
CREATE TABLE #TempDepartments (
 id int,
 department VARCHAR (200),
 parent int
 );

INSERT INTO #TempDepartments
SELECT TOP 5 * FROM [Departments];

SELECT *
 FROM [Departments] d
 INNER JOIN #TempDepartments td
    ON d.department = td.department COLLATE Latin1_General_CI_AS;

DROP TABLE #TempDepartments;

Another option would be to use the database default collation on both.

use [CollationTest];
CREATE TABLE #TempDepartments (
 id int,
 department VARCHAR (200),
 parent int
 );

INSERT INTO #TempDepartments
SELECT TOP 5 * FROM [Departments];

SELECT *
FROM [Departments] d
INNER JOIN #TempDepartments td
   ON d.department COLLATE DATABASE_DEFAULT
   = td.department COLLATE DATABASE_DEFAULT;

DROP TABLE #TempDepartments;

Keep in mind that it is recommended that the collation of your database match that of TempDB, but if they don’t you can use the above alternatives to make it work.

 

Similar errors:

Cannot resolve the collation conflict between “sql_latin1_general_cp437_ci_as” and “latin1_general_ci_as” in the equal to operation.
Cannot resolve the collation conflict between “sql_latin1_general_cp437_ci_as” and “sql_latin1_general_cp437_ci_as” in the union operation.

Sessions submitted to SQL Saturday Vancouver BC

I had so much fun at SQL Saturday in Vancouver BC last year, I have decided to go again.

The sessions submitted are:

The “Run Faster” Switch

The NO_LOCK hint is often believed to be the “Run Faster” switch, but in reality can cause some very inconsistent results.   Explore alternatives to the NO_LOCK hint to get your queries to run faster.  Will READ_UNCOMMITTED help?  What about Snapshot Isolation Levels?  Then there is Read Committed Snapshot?  What impact does a function have in your query?  Join Steve Stedman as he dives into safe ways to get your queries to run faster.

Exploring the TSQL Enhancements in SQL Server 2012

SQL Server 2012 adds many new enhancements to TSQL. Learn how to use the windowing functionality of the OVER clause to create sliding aggregation. Find out about the new SEQUENCE object and when you should or should not use it to replace an IDENTITY. Learn the IIF function and CHOOSE function to replace a common simple CASE functions. OFFSET / FETCH will be covered to show you how to eliminate cursors or CTEs for data paging. With CONCAT you have an easier time building strings in your TSQL statements independent of the data type, and without concern over nulls. With these and other new TSQL enhancements you will be able to take full advantage of the power of TSQL in SQL Server 2012.

Unleashing Common Table Expressions in SQL Server

Have you ever wanted to create a recursive query, but didn’t see how to do it. With the Common Table Expressions class you will learn everything needed to start using CTE’s for recursive queries, as temporary views, and to use the result set multiple times in the same query. Learn how simplify query syntax using CTE’s. One of the most overlooked features of SQL Server is the CTE which not only simplifies the query, but gives you the ability to do things that would otherwise be impossible (or at least very challenging) with SQL Server. Learn how recursive CTE’s perform better than other methods to create hierarchical results. See how CTE’s compare to SQL Server 2012 offset and fetch paging techniques.

SQL Saturday in Vancouver was a blast last year.  This is going to be a great time.