Collation: Temporary objects and variables: Demonstration

4

April 21, 2014 by Kenneth Fisher

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')
GO
-- Create a database with a different collation
CREATE DATABASE CollationTest 
	COLLATE Latin1_General_100_CS_AS_KS_WS_SC
GO

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
GO
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'
DROP TABLE #TempTable
GO

You can see in the results that the temp table uses the collation of the tempdb database (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.

CollationDemonstration1

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
GO
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')

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

DROP TABLE #TempTable
GO

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

4 thoughts on “Collation: Temporary objects and variables: Demonstration

  1. Jeremy says:

    “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.”

    I believe the first instance of “table variable” in the above statement should be temp table. Good post!

  2. David McKinney. says:

    When there’s a risk of this happening, you can use COLLATE DATABASE DEFAULT when creating temp tables so that they are created with the same collation as the database your creating them from. I prefer that to specifying an explicit collation which may in time change.

    Apologies if you’ve already covered this somewhere else.

    Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,674 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: