SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Collation: Temporary objects and variables: Demonstration

I had a recent run in with collation problems and it got me started reading about them. As I read I started to realize that for a seemingly simple subject there was a lot to it. So I thought I would go over some of what I found out.

I’ve talked about and alluded to the differences in collation defaults between temporary tables and table variables in a number of different posts but this time I thought I would do a brief demonstration.

First we need to confirm what our server collation is and create a database with a different collation.

-- Confirm the server collation
SELECT serverproperty('collation')
-- Create a database with a different collation
	COLLATE Latin1_General_100_CS_AS_KS_WS_SC

Next we go into the database and create a temp table and declare a table variable. Note that the actual structures for both are stored in tempdb. Unfortunately however the object names are not so easily found. You can find the object id of a temp table by using object_id(‘tempdb.dbo.#temptable’) but that won’t work for a table variable. Fortunately the column names are the same as what is defined so if we name our columns something fairly unique they will be easy enough to find in tempdb.sys.columns

USE CollationTest
CREATE TABLE #TempTable (TempTableCol1 char(10))
DECLARE @TableVar AS TABLE (TableVarCol1 char(10))

SELECT name, collation_name FROM tempdb.sys.columns where name like '%Col1'

You can see in the results that the table variable uses the collation of the tempdb (which is the same as the server collation) and the table variable uses the collation of the database you are connected to when it is declared. Now this is the default collation and as I said in Collation: Definition you can easily override the default by using the COLLATE clause when defining your table, be it physical, temporary or a variable.


Just for completeness here is a quick test joining the two tables together. Because the collations are so different I would expect to get a collation error.

 USE CollationTest
CREATE TABLE #TempTable (TempTableCol1 char(10))
DECLARE @TableVar AS TABLE (TableVarCol1 char(10))
INSERT INTO #TempTable VALUES ('abc'),('def'),('hij')
INSERT INTO @TableVar VALUES ('abc'),('def'),('hij')

FROM #TempTable TempTable
JOIN @TableVar TableVar
	ON TempTable.TempTableCol1 = TableVar.TableVarCol1


And in fact here is the error that you get when you try to match the two collations against each other.

Msg 468, Level 16, State 9, Line 6
Cannot resolve the collation conflict between “Latin1_General_100_CS_AS_KS_WS_SC” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.

Over a number of different posts I’m going to discuss some of the surprisingly deep subject of collation.

As a start listen to Rob Farley’s hilarious talk on collation
Collation: Definition
Collation: Location
Collation: Expression
Collation: Correction -> Expression, Column, Database
Collation: Correction -> Instance
Collation: Confusion

Filed under: Collation, Microsoft SQL Server, SQLServerPedia Syndication, System Functions and Stored Procedures, T-SQL Tagged: code language, collation, language sql, microsoft sql server, sql statements, system functions, T-SQL


My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.


Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...