How to create SQL Server temp tables without collation problems

In SQL Server it is possible to create temporary tables which are special tables that are automatically cleaned up when they go out of scope. There are two types of temporary tables, global and local temporary tables. Global Temporary Tables are visible in all sessions currently connected to the SQL Server instance and only go out of scope when the last session disconnects from the server. It is easy to recognise global temporary tables because their name starts with 2 #-signs. (for example "##customers") Local Temporary Tables are visible only in the current session and go out of scope when this session ends. Their name always starts with a single #-sign. (for example "#customer") Traditionally people create temporary tables the same way as they create normal tables

This seems like a perfectly reasonable way to create a temp table. Unfortunately SQL Server has one big flaw.

Presume we have the following situation. My SQL Server is installed with a Latin1_General_Bin collation and we create a database with a different collation.

Then we create a table in this database with some details about Rock Stars and a temp table with additional info.

This looks like a perfectly valid situation, but when we run the following query the problems start:

Msg 468, Level 16, State 9, Line 2
Cannot resolve the collation conflict between "Latin1_General_BIN" and "Latin1_General_CI_AI_KS_WS" in the equal to operation.

When we inspect the metadata closely we find the following:

strangeCollationDb RockStars name Latin1_General_CI_AI_KS_WS
tempdb #RockStarInfo... name Latin1_General_BIN

For some reason SQL Server will create temporary tables using the collation of tempdb instead of inheriting the collation of the current db in the connection context.

But all is not lost. If we simply use a different approach to creating our temporary table we can solve all collation problems without adding collate statements to our code. Besides using the create table statement it is also possible to use SELECT INTO to create a table.

1 Mick Jagger USA Mick Jagger Alive and kicking
2 Jim Morrison Heaven or Hell Jim Morrison Passed away

This time no errors were raised and if we check the metadata in tempdb we can see that the collation is the same as the collation in the source db.

tempdb #RockStarInfo2... name Latin1_General_CI_AI_KS_WS

The End... or not completely...

When all you use are the base system datatypes the problems are solved, unfortunately when you want to use alias data types the syntax I put in the example does not work since you can only use CAST() with system data types.

Fortunately a (bit more convulated) solution exists for this as well.

1 Jeff Buckley Heaven Jeff Buckley Drowned sadly
2 Neil Young USA Neil Young Keeps on rocking

As you can see, it is not possible to use the alias data type directly in the SELECT INTO statement, but it is possible to declare a variable with the datatype and then use the variable in the SELECT INTO statement.

And again, when we check the table layout in tempdb, we can see the collation has been inherited.

tempdb #RockStarInfo3... name Latin1_General_CI_AI_KS_WS

When you start supporting the internationalization features of SQL Server, many of the development practices you have learned and applied for years become invalid. And it is important to test your software in "non standard" installations like databases deployed with different collations.