Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Local Temporary Tables and Table Variables

By Peter He, (first published: 2008/09/01)

Local Temporary Table and Table Variable

There are lots of articles, blogs, forums discussed the differences and similarities between table variables and local temporary tables (ref. as temp tables hereafter). E.g.:

A summarization of table variable vs. temp table is attached in the appendix. Instead of repeating those well known facts, I'll focus on several differences that are hardly mentioned so far between table variable and temp table definitions.

XML Collection

The AdventureWorks sample database has an xml collection HumanResources.HRResumeSchemaCollection, which is used by the [Resume] column in the table HumanResources.JobCandidate.

The following script queries all the resumes into a table variable:

USE AdventureWorks
GO
DECLARE @TestXmls TABLE
(JobCandidateID int NOT NULL, [Resume] xml(HumanResources.HRResumeSchemaCollection) PRIMARY KEY CLUSTERED(JobCandidateID))

INSERT @TestXmls (JobCandidateID,[Resume])

SELECT JobCandidateID,[Resume] FROM HumanResources.JobCandidate
SELECT * FROM @TestXmls

GO

USE AdventureWorks
GO

It works perfectly. However, if you change the table variable to a temp table:

CREATE TABLE #TestXmls (JobCandidateID int NOT NULL,
  [Resume] xml(HumanResources.HRResumeSchemaCollection) 
  PRIMARY KEY CLUSTERED(JobCandidateID))

You will get the following error:

Msg 6314, Level 16, State 1, Line 2

Collection specified does not exist in metadata : 'HumanResources.HRResumeSchemaCollection'

This is because a temp table is created in TempDB, which does not have the xml collection HumanResources.HRResumeSchemaCollection. Though you can create the xml collection in the TempDB, it's not practical because:

  • TempDB is recreated whenever SQL server restarts;
  • TempDB is shared by all user/system databases and it is easily to have naming conflicts;

The work around is to use un-typed xml in the temporary table.

CREATE TABLE #TestXmls (JobCandidateID int NOT NULL,
  [Resume] xml PRIMARY KEY CLUSTERED(JobCandidateID))

INSERT #TestXmls (JobCandidateID,[Resume])

SELECT JobCandidateID,[Resume] FROM HumanResources.JobCandidate
SELECT * FROM #TestXmls
GO

User Defined DataTypes and User Defined Types (UDTs)

A user defined data type and UDT (CLR type) is similar to xml collection. It only resides in the database it is created. The AdventureWorks sample database has a user defined data type dbo.name, whose native data type is nvarchar(50). The following script queries all the address type names:

USE AdventureWorks
GO
DECLARE @TestUDTs TABLE (AddressTypeID int NOT NULL,
  [Name] dbo.Name NOT NULL PRIMARY KEY CLUSTERED(AddressTypeID))

INSERT @TestUDTs(AddressTypeID,[Name])

SELECT AddressTypeID,[Name]
 FROM Person.AddressType
SELECT * FROM @TestUDTs

It works fine. If you change to use temp table:

CREATE TABLE #TestUDTs (AddressTypeID int NOT NULL,
[Name] nvarchar(50) NOT NULL PRIMARY KEY CLUSTERED(AddressTypeID))

It will fail with the following error:

Msg 2715, Level 16, State 7, Line 1

Column, parameter, or variable #2: Cannot find data type dbo.Name.

The work around is to use the native data type of the user defined data type:

CREATE TABLE #TestUDTs (AddressTypeID int
NOT NULL,
[Name] nvarchar(50) NOT NULL PRIMARY KEY CLUSTERED(AddressTypeID))

UDTs are also scoped to a single database. If you use UDTs in a table variable, they are used in the same way as native data types. However since there is no equivalent native data type, if you need to use a UDT in a temp table, the UDT must be registered in tempdb the same way as for a user database, i.e. you need to CREATE the ASSEMLY and CREATE the TYPE in the TempDB. Due to the reasons mentioned in XML Collection

Collation

The collations for the string type (char, nchar, varchar, nvarchar) columns, if not given explicitly, will take the database collation of TempDB in a temp table, and inherit the collation of the current user database in a table variable. If not handled correctly, string comparing will fail.

Let's first find out the collation of the TempDB:

SELECT collation_name FROM sys.databases
WHERE database_id = DB_ID('tempdb')

To list all available collations:

SELECT * FROM fn_helpcollations()

Now let's create a test database, remember to use a different collation than the TempDB:

CREATE DATABASE CollateTest COLLATE Albanian_BIN
GO
USE CollateTest
GO

Create a table and populate some data:

CREATE TABLE dbo.Test1 (C2 int NOT NULL, C1 nvarchar(100) NOT NULL PRIMARY KEY CLUSTERED(C2))

GO

INSERT dbo.Test1 (C1,C2) SELECT N'R1',100 UNION ALL SELECT N'R2',200

GO

Now use a table variable to join with the table:

DECLARE @TestCollations TABLE (RID int identity not null, [Name] nvarchar(100) NOT NULL
    PRIMARY KEY CLUSTERED(RID))

INSERT @TestCollations ([Name]) VALUES (N'R2')
INSERT @TestCollations ([Name]) VALUES (N'R3')

SELECT A.* FROM dbo.Test1 A, @TestCollations B WHERE A.C1=B.[Name]
GO

It works fine. If you change to use temp table:

CREATE TABLE #TestCollations (RID int identity not null, [Name] nvarchar(100) NOT NULL
    PRIMARY KEY CLUSTERED(RID))

INSERT #TestCollations ([Name]) VALUES (N'R2')
INSERT #TestCollations ([Name]) VALUES (N'R3')

SELECT A.* FROM dbo.Test1 A, #TestCollations B WHERE A.C1=B.[Name]
GO

You get the following error:

Msg 468, Level 16, State 9, Line 6

Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "Albanian_BIN" in the equal to operation.

There are two workarounds. One is to force the collation conversion in the string comparing:

SELECT A.* 
 FROM dbo.Test1 A, #TestCollations B 
  WHERE A.C1=B.[Name] collate database_default

However it's a little bit annoying if you have lots of string comparing in your database. A better solution is to explicitly give the collation for the string column in the temp table:

IF object_id('tempdb..#TestCollations') IS NOT NULL
 DROP TABLE #TestCollations
GO
CREATE TABLE #TestCollations (
  RID int identity not null, 
  [Name] nvarchar(100) collate database_default NOT NULL 
  PRIMARY KEY CLUSTERED(RID))

INSERT #TestCollations ([Name]) VALUES (N'R2')
INSERT #TestCollations ([Name]) VALUES (N'R3')

SELECT A.* FROM dbo.Test1 A, #TestCollations B WHERE A.C1=B.[Name]
GO

The "collate database_default" clause in the column definition forces SQL Server to take current database(CollateTest)'s collation instead of TempDB's for the temp table.

If a SQL Server hosts multiple databases, or you need to restore external databases to the server, you can easily run into the collation conflict issue if the user databases use temp tables and string columns do not declare collations properly.

Conclusion

If you use table variables, you are free to use user defined data types, user defined types (UDTs), and xml collections that are defined in your database. You don't need to worry about collation conflicts. If you have to use a temp table due to various reasons, please keep in mind the following best practices:

  • Always attach "collate database_default" clause for string columns in a temp table.
  • Always use un-typed xml for xml columns in a temp table.
  • Always use the equivalent native data type for a user defined data type in a temp table.
  • If you need to use UDTs in a temp table, you have to register the type in TempDB.

 Appendix

Table Variable vs. Temp Table Summary:

Feature

Table Variable

Temp Table

Note

Table Name

Max 128 characters

Max 116 characters

 

Data Storage

In memory and TempDB

TempDB

 

Meta Data

In memory

TempDB

A table variable inherits current database settings and can use the registered UDTs, user defined data types, and xml collections in the database. A temp table inherits the settings of TempDB and cannot use the types created in the user database if the same types do not exist in the TempDB.

Scope

Current batch

Current session

Temp tables created in a stored procedure (SP) can be referenced by dynamic queries in the SP, sub SPs, triggers fired by the affected tables of the SP.

Constraints

Allowed

Allowed

For table variables, since no DDL is allowed, constraints can not be created in separate DDL statements.

DDL

Not allowed

Allowed.

E.g. create Index on the temp table.

Concurrent

Supported

Supported

Constraints and Indexes with explicit name in a temp table cause duplicate name error.

Statistics

Not supported

Supported

Estimated row number in execution plan for table variable is always 1

Parallel execution plan

Supported only for select

Supported

Parallel query execution plans are not generated for queries that modify table variables.

Transaction and Locking

Not participated

Participated

Data in table variable is not affected if the transaction is rolled back

Cause Recompile

No

Yes

Temp Table creation causes SPs/batches to recompile

SELECT INTO <t>

Not supported

Supported

 

INSERT <t> EXEC

Not supported

Supported

 

Use

UDFs, Stored procedures, Triggers, Batches

Stored procedures, Triggers, Batches

 Temp tables can't be used in UDFs.

Total article views: 27914 | Views in the last 30 days: 3
 
Related Articles
FORUM

Collation issue between tempdb and the database xxx - Better Solution?

Collation issue between tempdb and the database xxx - Better Solution?

FORUM

Collation

Incorrect Collation

SCRIPT

Show all database's tables' columns' collations.

Created view displays all text columns' collations in the current database with the information whet...

FORUM

TempDB

Tempdb

FORUM

Effect of collation change on tempdb space consumption

Effect of collation change on tempdb space consumption

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones