Cannot Resolve the Collation Conflict (Error 468)

Nothing is broken when you see 468. Both columns are valid, both databases are healthy, and the same query very likely works somewhere else. What has happened is that a comparison has one side stored under one set of sorting and equality rules and the other side under a different set, and SQL Server refuses to guess which one should win.

It refuses for a good reason. Picking silently would mean deciding whether 'Smith' = 'SMITH' is true, and getting that wrong changes which rows come back rather than producing an error. An error you can fix in a minute is better than a result set that is quietly wrong.

Where the Second Collation Came From

Three sources account for almost every 468, and they are worth telling apart because the fix is the same but the reason it keeps happening is not.

Two queries tell you which one you are in. The first shows every database collation on the instance, including tempdb:

SELECT name, collation_name
FROM   sys.databases
ORDER  BY name;

The second shows the collation of the columns actually in the comparison, which is what matters when two columns in the same database disagree:

SELECT  OBJECT_NAME(c.object_id) AS table_name,
        c.name                   AS column_name,
        c.collation_name
FROM    sys.columns AS c
WHERE   c.collation_name IS NOT NULL
  AND   OBJECT_NAME(c.object_id) IN ('Customers', 'Orders')
ORDER   BY table_name, column_name;

The Fix, and Why It Goes on the Comparison

COLLATE DATABASE_DEFAULT on one side of the comparison resolves it. It tells SQL Server to evaluate that expression using the collation of the current database, so both sides agree for the length of that comparison and nothing else changes:

SELECT  c.customer_id, o.order_id
FROM    Sales.dbo.Customers AS c
JOIN    Archive.dbo.Orders  AS o
        ON o.customer_code = c.customer_code COLLATE DATABASE_DEFAULT;

DATABASE_DEFAULT rather than a named collation is the deliberate part. Naming Latin1_General_CI_AS works today and hard-codes an assumption that survives until the query runs somewhere with a different default. DATABASE_DEFAULT keeps working after a restore to another instance, which is exactly the journey that produced the conflict in the first place.

For the temp table case, fix it where the table is declared rather than at every join to it:

CREATE TABLE #staging
(
    customer_code varchar(20) COLLATE DATABASE_DEFAULT NOT NULL,
    amount        decimal(18,2) NOT NULL
);

One declaration, and every subsequent join to that table stops caring.

What Not To Do

• Do not ALTER COLUMN to change the collation because one query failed. It rewrites the column, and it is blocked outright while an index, a computed column, a check constraint or a foreign key depends on it. You end up dropping and recreating objects during a change window to fix a query you could have fixed in one line.
• Do not rebuild the database collation to match. Changing a database’s collation does not change the collation of columns already in it, so the conflict survives the operation that was supposed to end it.
• Do not sprinkle a named collation everywhere. It clears today’s error and moves the problem to the next instance, where the name no longer matches the default.
• Do not wrap the column instead of the comparison if you can avoid it. WHERE c.code COLLATE DATABASE_DEFAULT = @x applies a function to the column and gives up the index seek. Collate the other side where you have the choice, for the same reason implicit conversions hurt.

Common Questions

Related Scripts

• SQL Server Collation: Case Sensitivity and the Cross-Database Trap, what collation actually controls, and a case-sensitivity test you can run
• SQL Server Errors: The Complete Guide, the index for the whole error series
• Invalid Object Name (Error 208), the other error that is usually about where you are rather than what exists