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:
- Frequently Asked Questions - SQL Server 2000 - Table Variables
- 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:
- Don't consider TempDB use
- 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)
- 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.