After being hit with numerous collation problems on a previous project, I’ve been very careful about never explicitly stating the collation in my SQL scripts. I just make sure that the database default collation is set correctly, and all tables automatically have the correct collation. Since then, I’ve managed to avoid any collation problems. Until yesterday that is! Some background: I managed to mess up the collation when I was installing SQL Server 2005 on my laptop some months ago. As a result, most of my databases have a different collation than the server they are running on. Normally, this isn’t a problem, but you should be careful when you’re working with temp tables.

When you explicitly create the temp table

CREATE TABLE #MyTempTable (
name varchar(30),
firstname varchar(20)
)

INSERT INTO #MyTempTable(name, firstname)
SELECT name, firstName
FROM somewhere

the table will use the collation of the tempdb (where the table is actually created) not that of your current database! Typically, the collation of tempdb is that of the database server, so in my case, where the database collation differs from the server collation, trying to insert entries from the temp table into another table:

INSERT INTO Person(name, firstName)
SELECT name, firstName
FROM #MyTempTable
EXCEPT
SELECT name, firstName
FROM Person

results in the following error

Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the EXCEPT operation.

The easiest way to fix this is to let SQL Server create the temp table for you via a SELECT INTO statement:

SELECT name, firstName
FROM somewhere
INTO #MyTempTable

The temp table will still be created in tempdb, but it’s fields will take the collation from their source (in this case, the fields in the <somewhere> table.

Of course, the SELECT INTO statement has the additional benefit that you don’t have to keep the temp table structure up-to-date manually!