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.

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