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

CREATE TABLE #RockAndRoll(
  ID           int     primary key 
  SongTitle    nvarchar(20),
  Genre        nvarchar(200),
  Sales        int
)

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.

CREATE DATABASE strangeCollationDb COLLATE Latin1_General_CI_AI_KS_WS
GO
USE strangeCollationDb 

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

CREATE TABLE RockStars (id int, name varchar(50), location varchar(400))
INSERT INTO RockStars VALUES (1, 'Mick Jagger', 'USA')
INSERT INTO RockStars VALUES (2, 'Jim Morrison', 'Heaven or Hell')

CREATE TABLE #RockStarInfo (name varchar(50), information varchar(400))
INSERT INTO #RockStarInfo VALUES ('Mick Jagger', 'Alive and kicking')
INSERT INTO #RockStarInfo VALUES ('Jim Morrison', 'Passed away')

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

SELECT * 
FROM Rockstars s
  LEFT JOIN #RockStarInfo i
  ON s.name = i.name

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:

SELECT db_name() as db, TABLE_NAME, COLUMN_NAME, COLLATION_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME   = 'RockStars' 
  AND TABLE_SCHEMA = current_user
  AND COLUMN_NAME  = 'name'
 
SELECT 'tempdb' as db, TABLE_NAME, COLUMN_NAME, COLLATION_NAME 
FROM tempdb.INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME   like '#RockStarInfo%' 
  AND TABLE_SCHEMA = current_user
  AND COLUMN_NAME  = 'name'

db	TABLE_NAME	COLUMN_NAME	COLLATION_NAME
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.

SELECT cast(null as varchar(50)) as name, cast(null as varchar(400)) as information
INTO #RockStarInfo2 
INSERT INTO #RockStarInfo2 VALUES ('Mick Jagger', 'Alive and kicking')
INSERT INTO #RockStarInfo2 VALUES ('Jim Morrison', 'Passed away')

SELECT * 
FROM Rockstars s
  LEFT JOIN #RockStarInfo2 i
  ON s.name = i.name

id	name	location	name	information
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.

SELECT 'tempdb' as db, TABLE_NAME, COLUMN_NAME, COLLATION_NAME 
FROM tempdb.INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME   like '#RockStarInfo2%' 
  AND TABLE_SCHEMA = current_user
  AND COLUMN_NAME  = 'name'

db	TABLE_NAME	COLUMN_NAME	COLLATION_NAME
tempdb	#RockStarInfo...	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.

CREATE TYPE NameType FROM varchar(50)
CREATE TYPE LocationType FROM varchar(50)

CREATE TABLE MoreRockStars (id int, name NameType, location LocationType)
INSERT INTO MoreRockStars VALUES (1, 'Jeff Buckley', 'Heaven')
INSERT INTO MoreRockStars VALUES (2, 'Neil Young', 'USA')

DECLARE @Name     NameType, 
        @Location LocationType

SELECT @Name as name, @Location as information into #RockStarInfo3 
INSERT INTO #RockStarInfo3 VALUES ('Jeff Buckley', 'Drowned sadly')
INSERT INTO #RockStarInfo3 VALUES ('Neil Young', 'Keeps on rocking')

SELECT * 
FROM MoreRockstars s
  LEFT JOIN #RockStarInfo3 i
  ON s.name = i.name

id	name	location	name	information
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.

SELECT 'tempdb' as db, TABLE_NAME, COLUMN_NAME, COLLATION_NAME 
FROM tempdb.INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME   like '#RockStarInfo3%' 
  AND TABLE_SCHEMA = current_user
  AND COLUMN_NAME  = 'name'

db	TABLE_NAME	COLUMN_NAME	COLLATION_NAME
tempdb	#RockStarInfo...	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.