SQL Server Temp Table vs Table Variable Performance Testing

Problem

If you've been a DBA for any amount of time I'm sure you've been asked the question:
Which is better to use, a SQL Server temp table or a table variable? There are technical
reasons why to use one over the other, but being that I am usually just interested
in performance I am more concerned with which one will be faster and use fewer resources.
In this tip we will compare the performance of a temp table vs a table variable
using a few straightforward scenarios.

Solution

For those of us that aren't familiar with all the technical differences between
the two objects
here is a great tip that covers all you need to know regarding the differences
between temporary tables and table variables.

For this test scenario we are going to load data into four tables, two will be
temporary tables and two will be table variables. Each of these object groups will
have one small table with only 2000 records and one larger one with 1000000 records
so we can see if there are any differences based on the size of the temporary object
as well.

To test the performance of these objects we are going to run statements for each
of the 4 basic DML operations, SELECT/INSERT/UPDATE/DELETE. For each operation we
are going run statements that effect both single and multiple rows. We will also
break up the statements so that each operation will test referencing primary key
columns as well as non-key columns that are both indexed and not indexed. This will
allow us to see how much benefit if any we get from having indexes on a column.
Only temporary tables can have additional non-clustered indexes defined. Table variables
can only have primary keys defined at creation so this may be an important factor
to consider when it comes to performance. The comments inline within the script
define what the statements are testing. Here is the code for all of our test scenarios.

-- simple insert
INSERT INTO #temptable (col1, col2, col3, col4) VALUES (2005,2005,2005,'TEST')
INSERT INTO @tablevariable (col1, col2, col3, col4) VALUES (2005,2005,2005,'TEST')
INSERT INTO #bigtemptable (col1, col2, col3, col4) VALUES (1000005,1000005,1000005,'TEST')
INSERT INTO @bigtablevariable (col1, col2, col3, col4) VALUES (1000005,1000005,1000005,'TEST')
-- select single row where primary key column equals value
SELECT * FROM #temptable where col1=1000
SELECT * FROM @tablevariable where col1=1000
SELECT * FROM #bigtemptable where col1=600000
SELECT * FROM @bigtablevariable where col1=600000
-- select range of rows where primary key column between values
SELECT * FROM #temptable where col1 between 450 and 500
SELECT * FROM @tablevariable where col1 between 450 and 500
SELECT * FROM #bigtemptable where col1 between 100000 and 100500
SELECT * FROM @bigtablevariable where col1 between 100000 and 100500
-- select single row where other indexed column equals value
SELECT * FROM #temptable where col2=1050
SELECT * FROM @tablevariable where col2=1050
SELECT * FROM #bigtemptable where col2=650000
SELECT * FROM @bigtablevariable where col2=650000
-- select range of rows where other indexed column between values
SELECT * FROM #temptable where col2 between 500 and 550
SELECT * FROM @tablevariable where col2 between 500 and 550
SELECT * FROM #bigtemptable where col2 between 150000 and 150500
SELECT * FROM @bigtablevariable where col2 between 150000 and 150500
-- select single row where other nonindexed column equals value
SELECT * FROM #temptable where col3=1100
SELECT * FROM @tablevariable where col3=1100
SELECT * FROM #bigtemptable where col3=700000
SELECT * FROM @bigtablevariable where col3=700000
-- select range of rows where other nonindexed column between values
SELECT * FROM #temptable where col3 between 550 and 600
SELECT * FROM @tablevariable where col3 between 550 and 600
SELECT * FROM #bigtemptable where col3 between 200000 and 200500
SELECT * FROM @bigtablevariable where col3 between 200000 and 200500
-- update single row where primary key column equals value
UPDATE #temptable set col4='TESTUPDATE' where col1=1150
UPDATE @tablevariable set col4='TESTUPDATE' where col1=1150
UPDATE #bigtemptable set col4='TESTUPDATE' where col1=750000
UPDATE @bigtablevariable set col4='TESTUPDATE' where col1=750000
-- update range of rows where primary key column between values
UPDATE #temptable set col4='TESTUPDATE' where col1 between 600 and 650
UPDATE @tablevariable set col4='TESTUPDATE' where col1 between 600 and 650
UPDATE #bigtemptable set col4='TESTUPDATE' where col1 between 250000 and 250500
UPDATE @bigtablevariable set col4='TESTUPDATE' where col1 between 250000 and 250500
-- update single row where other indexed column equals value
UPDATE #temptable set col4='TESTUPDATE' where col2=1200
UPDATE @tablevariable set col4='TESTUPDATE' where col2=1200
UPDATE #bigtemptable set col4='TESTUPDATE' where col2=800000
UPDATE @bigtablevariable set col4='TESTUPDATE' where col2=800000
-- update range of rows where other indexed column between values
UPDATE #temptable set col4='TESTUPDATE' where col2 between 650 and 700
UPDATE @tablevariable set col4='TESTUPDATE' where col2 between 650 and 700
UPDATE #bigtemptable set col4='TESTUPDATE' where col2 between 300000 and 300500
UPDATE @bigtablevariable set col4='TESTUPDATE' where col2 between 300000 and 300500
-- update single row where other nonindexed column equals value
UPDATE #temptable set col4='TESTUPDATE' where col3=1250
UPDATE @tablevariable set col4='TESTUPDATE' where col3=1250
UPDATE #bigtemptable set col4='TESTUPDATE' where col3=850000
UPDATE @bigtablevariable set col4='TESTUPDATE' where col3=850000
-- update range of rows where other nonindexed column between values
UPDATE #temptable set col4='TESTUPDATE' where col3 between 700 and 750
UPDATE @tablevariable set col4='TESTUPDATE' where col3 between 700 and 750
UPDATE #bigtemptable set col4='TESTUPDATE' where col3 between 350000 and 350500
UPDATE @bigtablevariable set col4='TESTUPDATE' where col3 between 350000 and 350500
-- delete single row where primary key column equals value
DELETE FROM #temptable where col1=1300
DELETE FROM @tablevariable where col1=1300
DELETE FROM #bigtemptable where col1=900000
DELETE FROM @bigtablevariable where col1=900000
-- delete range rows where primary key column between values
DELETE FROM #temptable where col1 between 750 and 800
DELETE FROM @tablevariable where col1 between 750 and 800
DELETE FROM #bigtemptable where col1 between 400000 and 400500
DELETE FROM @bigtablevariable where col1 between 400000 and 400500
-- delete single row where other indexed column equals value
DELETE FROM #temptable where col2=1350
DELETE FROM @tablevariable where col2=1350
DELETE FROM #bigtemptable where col2=950000
DELETE FROM @bigtablevariable where col2=950000
-- delete range of rows where other indexed column between values
DELETE FROM #temptable where col2 between 800 and 850
DELETE FROM @tablevariable where col2 between 800 and 850
DELETE FROM #bigtemptable where col2 between 450000 and 450500
DELETE FROM @bigtablevariable where col2 between 450000 and 450500
-- delete single row where other nonindexed column equals value
DELETE FROM #temptable where col3=1400
DELETE FROM @tablevariable where col3=1400
DELETE FROM #bigtemptable where col3=1000000
DELETE FROM @bigtablevariable where col3=1000000
-- delete range of rows where other nonindexed column values
DELETE FROM #temptable where col3 between 900 and 950
DELETE FROM @tablevariable where col3 between 900 and 950
DELETE FROM #bigtemptable where col3 between 500000 and 500500
DELETE FROM @bigtablevariable where col3 between 500000 and 500500

Note: This script
must be run in a single batch (table setup and test scenario) as the scope of the
table variables are limited to the current batch of sql statements.

Test Scenario Results for SQL Server Temp Tables vs. Table Variables

The
SQL Profiler trace from the
INSERT
statements show that the table variable outperforms the temporary table whether
dealing with a small or large temporary object. This can probably be attributed
to the fact that the temporary table has an extra index on the table that needs
to be updated as well.

The
SQL Profiler trace from the
SELECT
statements tell a different story. When querying rows based on the primary key column
or the non-indexed column we get the same performance from both objects. However,
when we query rows using the indexed column of the temporary table, which is not
indexed in the table variable since this is not available for table variables, we
see a really big increase in performance across all measures for the temporary table.
In fact, using this index to query a specific value from the temporary table yields
almost the same performance regardless of the size of the table.

The
SQL Profiler trace from the
UPDATE statements shows a similar trend to that of the SELECT statements. When
updating rows based on either the primary key or non-indexed column the performance
is similar. When updating rows based on the indexed column the temporary table performs
quite a bit better.

With the
SQL Profiler
trace for the
DELETE statements we see an interesting result. When deleting rows based on
the primary key the table variable outperforms the temporary table. As with the
SQL Profiler
results of the
INSERT
statement this is probably due to the fact that an extra index has to be updated.
As with the other scenarios when deleting based on the indexed column the temporary
table performs better than the table variable. When deleting rows based on the column
with no index we see similar performance between the two objects with the temporary
table only slightly better when dealing with a range of records. One thing I want
to add with regards
DELETEs is that I would not be as concerned with its performance since in most
cases if you don't need the data in your temporary object then you can exclude it
when performing the
SELECT/INSERT
rather than removing it after the fact.

As we can see from the results above a temporary table generally provides better
performance than a table variable. The only time this is not the case is when doing
an
INSERT and a few types of
DELETE conditions. This increase in performance is especially evident when dealing
with larger data sets as the ability to create indexes on the temporary table speeds
up query execution significantly. When the data set is small the difference in performance
is almost negligible. In my opinion if you are doing a lot of data manipulation
with the temporary object after it has been created and the data set is fairly large
a temporary table is the best option. As always it's best to test yourself to confirm
which object gives you the best performance for your particular application.

Next Steps

Perform further testing to check performance and determine the best option
for your use cases.

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter
I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.

I have found, that if my table is only used for looking up data, better to be a Table Variable then direct link to the table. I took a process that was taking HOURS to run to down to a matter of MINUTES just by copying what data I need from my TABLE into a TABLE VARIABLE. Yes, I am talking 7-8 hours of work was compressed into maybe 7 minutes. But these were only lookups tables. I was totally supprised by the increase in performance...oh and happy too.

-- -- they created a #temp table to hold only 3 records??-- CREATETABLE#DaysOfWeek ([Day] char(3))INSERTINTO#DaysOfWeek([Day])VALUES('Sun'),('Tue'),('Fri')-- -- a few queries with joins on #DaysOfWeek-- DROPTABLE#DaysOfWeek

There are a few other considerations here... but one I constantly deal with is parallelism.

If using a table variable, you are usually limited to a single thread, temp tables don't have this limitation. (I always used table variables under SQL2000 due to 'questionable' performance when multithreading, but SQL2008+ performs far better when multithreading)

When joining large tables together with a subset of data prepared in a temp table, the multithreading aspect greatly improves the performance of my operations (as long as I've written them properly) when using temp tables.

Okay so let's say we figure out that the best way to test for a table and it's querys(accessing data meaningfully) is a temp table. Okay, that is great. However, what do we do after that....do we create a 'regular' table? So, if we figured that a temp table (and it's queries that use that temp table) perform better in the sense of accessing data successfully(in regards to the desired data showing), what do we do next? Create a table? If so, what difference in the 'regular' tables creation would it make if the best way to test was to use table variables?

Meaning, if either temp tables or table variables were the 'winner' in the testing 'contest', wouldn't the devloper still need to create a regular table?

I understand that the developer also needs to look for the best way to query, so I have that down.

As the article and some of the comments mention there is no clear answer as to which is best to use in all cases, it all depends on your specific situation.

How a user views if something is of benefit is usually based solely on performance (they usually don't care how something is implemented behind the scenes) so as mentioned I would test which gives the best performance for your particular situation

So after all the dust is cleared (from performing testing using temp tables and table variables)....what is the final outcome of all the testing. Do you create an actual table containing all the columns and data types that was contained/tested in the temp table (or table variable)?

What is the main objective as far as how the user (not developer or DBA) benefits?

That is what I would like to know. I am new to the development 'game' and would like a conceptual explanation on coding and how it benefits the user.

my 2 cents the basic difference when we ask anybody is that table variables are memory resident whereas temporary tables are temdb resident which is very untrue which is been proven in many blog posts and can be proved with the below code snippet

use tempdb

---this would list all the existing #tables used by other applications can list down or can try it in a test environment

select * from sys.tables where name like '%#%'

GO-- a very small table

declare @table as TABLE

(

id int

)

select * from sys.tables where name like '%#%'

---You will see we have a # table which is for table variable---

But to me the question arises what is the reason of keeping table variable if we have a similar temprary table. I could only see the case of functions otherwise i could not get any reason where we can justify the existance of table variable.

Thanks for the excellent tip. This confirms what I have generally seen as well.

Personally, I think table variables often result in slightly cleaner code and they can be passed as parameters when that matters. But temp tables will almost always perform at least as well and often better, and they give you more options for indexing. Generally, I use a table variable when I am dealing with very small datasets (less than 100 rows) and temp tables the rest of the time.

Table variables are subject to logging because if in an error occurs during the execution of the statement it needs to be rolled back. For example

DECLARE @T TABLE

(

P INT PRIMARY KEY

)

INSERT INTO @T VALUES (1),(2)

INSERT INTO @T VALUES (3),(4),(1)

SELECT * FROM @T

The statement encounters a PK uniqueness violation so the insert of values 3 & 4 is rolled back.

In 2008+ "INSERT ... SELECT" can be minimally logged in the same way as "SELECT ... INTO". Temporary tables can still have advantages when it comes to logging though. e.g table variables do not support "TRUNCATE". One logging advantage of table variables is that the system transactions on them are independent of the surrounding user transaction so a long running transaction that writes to table variables will not prevent the tempdb transaction log from being truncated.

One considering when persisting and modifying large temporary resultsets is transaction logging. I understand why temp tables are logged, because they are included in batch level transactions and so technically should be included in a rollback, although at least in my experience there rarely a practical use for that. However, with temp tables you have the option of creating the table using SELECT INTO, which will not not transaction log the inserts the same way it will if you first create the table and then insert it.

Table variables are not even included in transactions, just like other variables they arn't recovered from a rollback, but for some reason they are still transaction logged like temp tables. That's unfortunate, and if it were not the case, table variables would be clearly favored in a wider array of scenarios.

This was a reasonable first test, and I'm glad to see actual numbers, but I'd have to suggest some other factors to look at, most of which are highly likely to favor #temp tables at many non-trivial data sizes.

1) For any unique column (even more with the unique, contiguous data you used) Table variable nonclustered unique indexes can be created using UNIQUE constraints (http://www.sqlservercentral.com/Forums/Topic535656-1338-1.aspx#bm535734)

3) Without any indexes at all on either - no primary key, no nothing. Particularly useful in the table scan cases Jeff mentioned

4) Multiple load phases - using temporary structures to run what's normally nasty row-by-row exception case logic only once for a given job. 4a) Often this is gathering just the required and relevant and accurate data from several tables to be worked on by many queries later. 4b) Combine with 2a's index rebuilds if required.

To answer the question about the conversion of large Table Variables to Temp Tables...

Both Temp Tables and Table Variables build their structure in TempDB. As with any good database engine, those structures start out in memory and spill to disk if they get too big. You can typically see Table Variables in TempDB as tables that begin with a # sign and have an all numeric digit name.

Nice article. Good to see someone quantify these differences with code.

In many (most?) cases, the temporary structure is made to contain only the data that is actually needed. The reason why that is important is because that usually means that full table scans will be made anyway which usually negates the need for any indexing. There are always exceptions, of course.

Whether there are indexes or not, Temp Tables can frequently be batch loaded with minimal logging using SELECT INTO which can give Temp Tables a performance advantage over Table Variables.

There is another "performance" advantage that Temp Tables can have over Table Variables and you eluded to it. The contents of a Temp Table will persist in SSMS allowing a programmer to incrimentally troubleshoot code without having to include all of the previous code that builds and populates a Table Variable. That sometimes makes both development and troubleshooting go quite a bit faster.

Nice to see someone else confirm my experience - e.g. I'm not crazy! From my testing, temp tables are as good as or outperform table variables. The clear advantage to me is the ability to pass a table variable as a parameter, but the use case for those seems limited, and the overhead (creating a table variable type) is too much. Easier to just use temp tables, even then.

I have heard that the optimizer / sql engine will convert large table variables to temporary tables anyway, under the covers. Can anyone confirm?

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.