Handling collation sequences with temporary tables and table variables with SQL Server

When building complex stored procedures that span multiple tables, you will probably need to store some intermediate results in a local buffer and process them before returning the final output.  SQL Server lets you do this through temporary table and table variables.  

Usually table variables offer faster performance (less locking and logging are required), but they have more restrictions than temporary tables.  Common to both types is where they are located.  When you create a table variable or temporary table, it gets created in the tempdb database, not in the current database.  This can affect the collation sequences applied to character fields  If the SQL Server was installed using one collation and your database uses a different collation, joins from tables in your database with temporary tables will fail if you join on character fields.  The tempdb database will use the server default collation sequence. That collation may not be the same collation used by your database if your created your database on a different server that used a difference collation.  The server collation is used for all of the system databases (including tempdb) and for any newly created user databases.  Databases that are attached or restored from a backup keep the collation that they were created with.

Gregory Larsen posted some sample SQL code on sqlservercentral.com that will demonstrate the error.  If you run the following SQL:

create table #a (char_set1 varchar(50) collate Latin1_General_CI_AS) 
create table #b(char_set2 varchar(50) collate Latin1_General_BIN) 
insert into #a values ('collate') 
insert into #b values ('collate') 
select * from #a join #b on char_set1 = char_set2

You will an error message like the following:

Msg 468, Level 16, State 9, Line 5

Cannot resolve the collation conflict between "Latin1_General_BIN" and "Latin1_General_CI_AS" in the equal to operation.

That example is used to show the type of error you would get in your code.  The actual code that would throw that error would be comparisons between character fields in a temporary table/table variable and with a permanent table in your database.  If you are deploying databases to servers where the server’s default collation sequence could be different than the collation sequence used by your database, then you want to add “COLLATE database_default” to all of your character field definitions when you define a temporary table or table variable.  Using “COLLATE Database_Default” will assign the collation sequence of the current database to the field.  This will allow field comparisons between character fields in temporary tables/table variables and permanent tables to execute with triggering the “collation conflict” error.

Instead of using syntax like:

create table #a(SomeID integer, SomeCharValue varchar(20)) 
  
declare @a TABLE(SomeID integer, SomeCharValue varchar(20)) 

Use the following:

create table #a(SomeID integer, SomeCharValue varchar(20) COLLATE database_default) 
  
declare @a TABLE(SomeID integer, SomeCharValue varchar(20) COLLATE database_default)

This will work no matter what the collation sequence for either tempdb or your database.  You don’t need to query the server to check what the sequence.  The big limitation is that you have to explicitly define the columns in the temporary table.  If you were using SELECT INTO syntax to create the temporary table implicitly by the columns in the SELECT statement, the temporary table will use the collation of tempdb.  You would need to structure the SQL.  For example, if you were using syntax like this:

select name, crdate, filename
into #a
from master.dbo.sysdatabases
drop table #a

You would need to rewrite it to look like this:

create table #a(
  name sysname collate database_default,
  crdate datetime,
  filename nvarchar(260) collate database_default
)
insert #a(name, crdate, filename)
select name, crdate, filename
from master.dbo.sysdatabases
drop table #a

You pay a small penalty by the extra SQL to explicitly define the temporary table, but you gain with having code that will work on any server.