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

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

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

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.

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: code language, microsoft sql server, T-SQL

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...