Fix collation problems on a database

If you restore a database onto a server whose default collation is different, two problems can arise:

• collation conflicts in cross-database queries that compare CHAR or VARCHAR columns, in a join clause for instance;
• collation conflicts in code that works with temporary tables.

Fixing the temporary table problem

When you create a temporary table — a table whose name starts with # — the collation of its CHAR and VARCHAR columns comes from the default collation of the tempdb database. If you then compare those columns with columns from the current database, you get error 468: Cannot resolve the collation conflict between....

The PachaDataFormation demo database uses French_CI_AS. On an instance whose collation is SQL_Latin1_General_CP1_CI_AS, which tempdb then shares, this join fails:

USE [PachaDataFormation];

CREATE TABLE #Contact (
    ContactId INT,
    Nom VARCHAR(50)
);

INSERT INTO #Contact (ContactId, Nom)
SELECT ContactId, Nom
FROM Contact.Contact
WHERE Nom LIKE 'Fera%';

SELECT c.ContactId, c.Nom, c.Prenom
FROM Contact.Contact AS c
JOIN #Contact AS t ON t.Nom = c.Nom;

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

There are two ways to fix this:

1. Create your temporary tables with an explicit collation, using the COLLATE clause and the DATABASE_DEFAULT keyword:

   USE [PachaDataFormation];
   
   DROP TABLE IF EXISTS #Contact;
   
   CREATE TABLE #Contact (
       ContactId INT,
       Nom VARCHAR(50) COLLATE DATABASE_DEFAULT
   );
   
   INSERT INTO #Contact (ContactId, Nom)
   SELECT ContactId, Nom
   FROM Contact.Contact
   WHERE Nom LIKE 'Fera%';
   
   SELECT c.ContactId, c.Nom, c.Prenom
   FROM Contact.Contact AS c
   JOIN #Contact AS t ON t.Nom = c.Nom;
   

   This applies the default collation of the current database to the Nom column, and the join no longer raises the error.

2. Use the Partially Contained Database feature. When a database is marked as contained, temporary tables created in its context take the collation of the database rather than that of tempdb.

Enabling the Partially Contained Database feature

First allow the feature at server level:

EXEC sys.sp_configure N'contained database authentication', N'1';
RECONFIGURE WITH OVERRIDE;
GO

Then enable it on the database. This requires exclusive access to the database, so we use the SINGLE_USER clause with the ROLLBACK IMMEDIATE option to force every user to disconnect and roll back any transaction in flight:

USE master;
GO
ALTER DATABASE [PachaDataFormation] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE [PachaDataFormation] SET CONTAINMENT = PARTIAL;
GO
ALTER DATABASE [PachaDataFormation] SET MULTI_USER;
GO