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....

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:

   CREATE TABLE #TempTable (
       Id INT,
       Name VARCHAR(50) COLLATE DATABASE_DEFAULT
   );
   

   This applies the default collation of the current database to the Name column.

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 [MyDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE [MyDatabase] SET CONTAINMENT = PARTIAL;
GO