Fixing Collation Conflicts Between tempdb and Your Database

A procedure joins a local temp table to a user table and fails with “Cannot resolve the collation conflict.” These collation conflicts start with mismatched text rules. In a non-contained database, tempdb follows the server collation while the user database can use another collation. Fix the temp column definition or the comparison deliberately, then test the intended string semantics.

Confirm the Three Collations

Read the server collation, current database collation, and tempdb collation. A difference between the user database and tempdb is a warning for character columns in temporary tables. The failure occurs when SQL Server must compare strings under incompatible implicit collations, not merely because two collations differ somewhere on the instance.

SELECT SERVERPROPERTY('Collation') AS server_collation,
       DATABASEPROPERTYEX(DB_NAME(),'Collation') AS database_collation,
       DATABASEPROPERTYEX('tempdb','Collation') AS tempdb_collation;

I check the actual column collations too. A permanent table column can override the database default, so database-level values alone do not prove the join's two columns match. Which expression does the error name, and what collation does each operand carry?

Reproduce Collation Conflicts in a Lab

Use a test database whose default collation differs from the instance default. Create a permanent table and a local temp table with unqualified varchar columns, then join them. The permanent column takes the user database default; the temp column takes tempdb's collation in a non-contained database. When the two collations differ, the final join fails on purpose with error 468. On an instance where both collations match, the demo does not fail.

CREATE TABLE dbo.CollationDemo
(
    Code varchar(20) NOT NULL PRIMARY KEY
);
CREATE TABLE #CodeTemp (Code varchar(20) NOT NULL);
INSERT dbo.CollationDemo VALUES ('ABC');
INSERT #CodeTemp VALUES ('ABC');
SELECT p.Code
FROM dbo.CollationDemo AS p
JOIN #CodeTemp AS t ON t.Code = p.Code;

Run it only in a disposable database and drop the objects afterward. In my lab the database used Latin1_General_100_CS_AS and tempdb used SQL_Latin1_General_CP1_CI_AS, and the join raised error 468 naming both. Do not change a production database's collation merely to run the example; that is a much broader operation with many dependencies.

Prefer DATABASE_DEFAULT at Temp Creation

Declare the temp column with COLLATE DATABASE_DEFAULT. SQL Server then uses the current user database's default collation for that column, avoiding this common mismatch. Put the clause on every temp character column that will be compared to user data. Create the temp table while the connection is in the intended user database.

CREATE TABLE #CodeTempFixed
(
    Code varchar(20) COLLATE DATABASE_DEFAULT NOT NULL
);
INSERT #CodeTempFixed VALUES ('ABC');
SELECT p.Code
FROM dbo.CollationDemo AS p
JOIN #CodeTempFixed AS t ON t.Code = p.Code;

If the permanent column has an explicit collation different from the database default, DATABASE_DEFAULT can still mismatch. Inspect sys.columns.collation_name for the actual column. I prefer a definition fix when the temp table is under my control, because it makes all later comparisons consistent without repeating COLLATE in every JOIN.

Use a Join-Level COLLATE When Needed

For a one-off query or a temp table created elsewhere, apply COLLATE in the join to choose a common collation. The example converts the temp expression to the current database default. This is clear but can affect index use if the conversion lands on an indexed column or changes comparison semantics.

SELECT p.Code
FROM dbo.CollationDemo AS p
JOIN #CodeTemp AS t
  ON t.Code COLLATE DATABASE_DEFAULT = p.Code;

A case-sensitive collation and a case-insensitive collation can give different matches. An accent-sensitive comparison can too. The fix should follow the application's key definition, not merely silence the error. Test representative values with case and accent differences, and read the actual plan before applying COLLATE to a hot query.

Compare Contained Databases

A contained database reduces dependence on the instance's tempdb collation. In a contained database, temporary table data defaults to the contained database's collation, so the unqualified temp-column example generally aligns with permanent user data. That is a database architecture choice, not a quick fix for one procedure. Containment changes metadata and authentication behavior that needs a separate review.

I do not convert a database to contained solely to avoid adding one COLLATE clause. The comparison is useful when planning migrations between servers with different instance collations. A contained database can carry more predictable temp-data collation behavior, while an ordinary database needs explicit handling.

Find Databases at Risk of Collation Conflicts

List databases whose collation differs from the instance collation. Exclude system databases from the application review, but show their values for context. This inventory finds places to test temp-table joins, not proof that every procedure there fails.

SELECT name, collation_name
FROM sys.databases
WHERE database_id > 4
  AND collation_name <> CONVERT(sysname,SERVERPROPERTY('Collation'))
ORDER BY name;

I sample procedures that create temp string columns and join them to permanent tables. Fix those definitions at the source and add a regression test on an instance with mismatched collations. The best result is a procedure whose comparison semantics are explicit and whose plan remains appropriate.

Inspect Column-Level Collation

A database default is only the starting point for a column. Query sys.columns.collation_name for the two columns in the failing equality. A permanent table can have an explicit collation from a migration years ago; adding DATABASE_DEFAULT to the temp column will still conflict in that case. Use the specific intended collation in the temp definition or a deliberate join expression, and test comparisons for case, accents, and width where relevant.

SELECT OBJECT_SCHEMA_NAME(c.object_id) AS schema_name,
       OBJECT_NAME(c.object_id) AS table_name,
       c.name AS column_name, c.collation_name
FROM sys.columns AS c
WHERE c.object_id = OBJECT_ID(N'dbo.CollationDemo')
  AND c.name = N'Code';

Check the Plan After Fixing Collation Conflicts

A COLLATE on the indexed permanent column can make an otherwise selective join scan or compute a value for many rows. Put the conversion on the smaller temp side when semantics allow, or define the temp column correctly at creation. Compare actual plans and logical reads before and after. A syntactically successful query can still be a production regression when it runs against millions of rows.

Inventory Procedures That Create Temp Strings

A catalog search for CREATE TABLE # and varchar or nvarchar declarations is only a discovery pass; procedure text can contain comments or dynamic SQL. Review the hot procedures in mismatched-collation databases first. I add a lab regression test that uses an instance collation different from the database and includes values whose case or accent behavior matters. That catches both error 468 and unintended equality changes.

The long-term policy can be a standard COLLATE DATABASE_DEFAULT on temp string columns that join to database-default user columns. Keep explicit exceptions for permanent columns with their own collation. Review those exceptions when databases move between instances.

Related reading on this blog: Resolve Cannot Resolve Collation Conflict Error: SQL in Sixty Seconds #047 and Change Database and Table Collation.

A collation fix is not a forced conversion everywhere, it is an alignment with intended comparison rules.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.