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

Things You Didn't Know About Temp Tables and Table Variables

By Roi Assa,

Introduction

There are a lot of articles comparing temp tables against table variables. Most articles talks about table variable vs. Temp tables - advantages and disadvantages (fewer compilations, indexes + statistics, nested procedures etc.)

For example here are two of them:

  1. Frequently Asked Questions - SQL Server 2000 - Table Variables
  2. Is a Temporary Table Really Necessary?

In this article I'll mention few things I didn't find in any other article and that you should keep in the back of your mind when thinking about one of those options.

Using TempDB

One of the common theories is that table variables do not use TempDB. Well, a table variable is created in TempDB, the same as a temp table. Both table variables and temp tables use the data cache (memory) if available, but can spill into tempdb. Since both of them reside in TempDB, when considering whether to use a temp table or a table variable, don’t take this into account.

You can run the script blow to find the data for the table variable in TempDB and show this:

CREATE TABLE #TempTable (TT_Col1 INT)

DECLARE@TableVariable TABLE (TV_Col1 INT)

SELECT TOP 2 *

FROM tempdb.sys.objects

ORDER BY create_date DESC

name

-----------------------------------------------------------------------------

#14E61A24

#TempTable_______________________________________________________00000000001E

Transactions

"Because table variables have a limited scope and are not part of the persistent database, transaction rollbacks do not affect them". You have to understand the meanings of this phrase: table variables are not influenced by transactions. It can be an advantage or disadvantage – depends on what you're trying to achieve, but most of the developers will find this fact a disadvantage since data integrity is affected. In other words when you're using transaction and table variable you should be fully aware that there are cases when the data you think you'll get is different from the actual represented data.

Let's run the scripts below to understand how transactions influence table variables and temp tables.

-- Insert value (1) into temp table and table variable

INSERT #TempTable VALUES (1)

INSERT@TableVariable VALUES (1)

-- Insert value (2) and rollback immediatly

BEGIN TRANSACTION

      INSERT #TempTable VALUES (2)

      INSERT @TableVariable VALUES (2)

ROLLBACK

-- Select from temp table

SELECT * FROM #TempTable

TT_Col1

-----------

1

-- Select from table variable – we except to get only 1 record

SELECT * FROM @TableVariable

TV_Col1

-----------

1

2

On the last query, you would expect to get only value TV_Col1=1 (because a rollback occurred on this insertion) and you got two records when one of those record was rolled back. This behavior can affect and hurt your results when you haven’t thought about it. For example, let's assume we are using the above code and joining it with another table for slicing; the results could be different than you expect, and you should be aware of that.

As I wrote before, it can be an advantage or disadvantage.

  • Disadvantage – data integrity!!! I'm sure not all DBA and developers are aware of this behavior, meaning there is a possibility that your code refers to the "wrong data."
  • Advantage – Since a table variable is out of the transaction scope, you can catch data transferred within a transaction, even if a rollback occurs.

Constraints

You can't add a constraint on a table variable, but you can add one on a temp table like any other table. There are few ways to add constraints (like a primary key) to a temp table. One of the options is to define a specific constraint name or use default name. Adding a constraint with specific name can raise errors and the reason is collision between object names.

For example:

-- Create table with default constraint name

CREATE TABLE #TempTableConstraint

(TTC_Col1 INT PRIMARY KEY)


-- Create table with explicity constraint name

CREATE TABLE #TempTableConstraintName

(TTCN_Col1 INT CONSTRAINT

PK_#TempTableConstraintName_TTCN_Col1 PRIMARY KEY

)

Those two options are valid. Now, run this code (both create statements) on different sessions and you'll get the error below:

Msg 2714, Level 16, State 4, Line 4

There is already an object named 'PK_#TempTableConstraintName_TTCN_Col1' in the database.

Msg 1750, Level 16, State 0, Line 4

Could not create constraint. See previous errors.

SQL Server knows how to define between 2 create temp table statements in different sessions but can't define constraints with same name. If you'll use constraint name within your code then the first execute will run smoothly while the second parallel execution will get the error above.

Let's assume you added the 2nd create table with an explicit constraint name to your stored procedure, and this procedure executed simultaneously from your application – the result will be an error code you didn't except to.

Therefore, when you'll use constraints within temp table, do not use explicit constraint name but use default name. For example, if you'd like to create a table with primary key (constraint default name):

CREATE TABLE #TempTableConstraint

(TTC_Col1 INT PRIMARY KEY)

Conclusion

When you are building your solution and need to choose whether to use a table variable or a temp table, make sure you are aware of all the differences and match the appropriate one to your needs. Be sure you remember the following:

  1. Don't consider TempDB use
  2. Transactions and table variable – if you're using a transaction (triggers are part of a transaction as well) and a table variable is in your code, be aware that a rollback is not affecting your data all of the time, when most of the time you'll expect it will. Use it wisely… (on rare cases you can use it as "feature" to log attempts to change your data within trigger)
  3. Constraints names on temp table – most of the time it will the be primary key name, but when you create temp table and grant explicit name to constraint, be aware that this name can cause you problems since it is represented as object in TempDB and objects must be unique (as opposed to temp tables when the name is represented in a different way on TempDB sysobjects).

Finally, I found out those issues very helpful for me when coding. I'm not using constraint names on temp tables anymore (learnt my lesson) and until now I didn't found a reason to not use default names. Regarding to transactions and table variable, I'm thinking twice when using transactions and table variables together (depends on what I'm trying to get) but at least the power to use it or not is my hands.

Total article views: 12533 | Views in the last 30 days: 21
 
Related Articles
FORUM

Transaction/Sec on tempdb

Question about the Transaction/Sec counter on the tempdb

FORUM

TempDB

Tempdb

FORUM

Table Variables and performance

About table variables and memory vs tempdb

FORUM

Pass Variable to Precendant Constraint

SSIS Variables!!&@*##

FORUM

Table variable does not take part in Transaction

Table variables does not take part in Transaction

 
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