May 072012
 

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

  One Response to “Collation Of Temp Tables”

  1. Very important and useful note.

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="">

(required)

(required)

C# | HTML | Plain Text | SQL | XHTML | XML | XSLT |

This site uses Akismet to reduce spam. Learn how your comment data is processed.