Temp tables are used frequently while coding. It might cause issues when the collation of user database is different from system default collation. The message you will see is
Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CS_AS” in the equal to operation.
Here are few tricks to member when you are in such environment. Temp objects are not always using server’s default collation code. Let’s test it out. Assume the default collation code is SQL_Latin1_General_CP1_CI_AS and database test1 is using Latin1_General_CS_AS.
Table Variables
use test1 if object_id('test') is not null drop table test go create table test(WeirdColumnName0 varchar(10)) insert into test values('a') declare @test table (WeirdColumnName1 varchar(10)) insert into @test values('a') select * from test1.dbo.test a inner join @test b on a.WeirdColumnName0 = b.WeirdColumnName1 (1 row(s) affected) (1 row(s) affected) WeirdColumnName0 WeirdColumnName1 ---------------- ---------------- a a (1 row(s) affected)
Ok, It seems that there is no conflict on table variables. Is that really true? Let’s run following code
use master declare @test table (WeirdColumnName1 varchar(10)) insert into @test values('a') select * from test1.dbo.test a inner join @test b on a.WeirdColumnName0 = b.WeirdColumnName1 /* Msg 468, Level 16, State 9, Line 6 Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CS_AS" in the equal to operation. */
You get collation conflict here. It’s conclusive that the collation used in table variables is the same as default collation of the connected database by default.
Temp Tables
if object_id('tempdb..#test') is not null drop table #test go create table #test(WeirdColumnName1 varchar(10)) insert into #test values('a') select * from test1.dbo.test a inner join #test b on a.WeirdColumnName0 = b.WeirdColumnName1 /* (1 row(s) affected) Msg 468, Level 16, State 9, Line 3 Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CS_AS" in the equal to operation. */
This is what you get. What if we create the temp tables in different way?
if object_id('tempdb..#test') is not null drop table #test go select WeirdColumnName0 as WeirdColumnName1 into #test from test1.dbo.test select * from test1.dbo.test a inner join #test b on a.WeirdColumnName0 = b.WeirdColumnName1 /* (1 row(s) affected) WeirdColumnName0 WeirdColumnName1 ---------------- ---------------- a a (1 row(s) affected) */
Ok, it works. This mean that, for temp tables, if the creation of temp table is from DDL without specifying any collation, the temp table will use instance default collation; if temp table is created by “select …into..”, the collation definitions are carried over
Very important and useful note.