What’s the difference between a temp table and a table variable?

3

February 24, 2014 by Kenneth Fisher

I recently saw an answer to this question on dba.stackexchange.com written by Martin Smith. It was probably one of the most complete answers to this question I have ever seen. In fact it’s probably one of the most complete answers possible. I highly recommend that you read it. In the mean time here is a summary:

  • Table variables are actually stored in tempdb just like temporary tables.
  • With respect to default collation, user defined data types and xml collections table variables act like they are part of the local database, temporary tables act like they are part of tempdb.
  • Temp tables have a much wider scope. If created at the outer scope (@@NESTLEVEL = 0) they can span batches.
  • Rollback will affect temp tables but not table variables.
  • Table variables do not support TRUNCATE.
  • Column statistics are maintained for temp tables not table variables.
  • Indexes: Prior to SQL 2014 table variables only support indexes if created implicitly by creating a unique constraint or primary key. Post SQL 2014 table variables do support non-clustered indexes when declared in line. Also table variables do not support INCLUDE columns, filtered indexes or partitioning. Temp tables seem to fully support indexing (although the partitioning scheme would have to be created in tempdb of course).
  • Queries with table variables don’t get parallel plans, the same is not true with temp tables.

3 thoughts on “What’s the difference between a temp table and a table variable?

  1. Hi, Nice post. Like to add a point that, query which updates table variable can’t have parallel plan, but select query can use parallel plan if cost exceeds degree of threshold. You might need to add option(recompile) to query to get correct actual number of rows, so that you can get correct estimated cost.

    Or use Option (QuerytraceOn 8649) to see its working.

    • Thanks for the added info. You should make a point of following the link (if you haven’t already) back to the original answer on stackexchange. I was just summarizing a few of the MANY points Martin made. He also had lot’s of great proofs and examples.

  2. […] This means that if you don’t specify the column collation it’s going to be case sensitive. Regardless of the database you are in a temp table uses the collation of tempdb. […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,146 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: