TempDB:: Table variable vs local temporary table

As you know the tempdb is used by user applications and SQL Server alike to store transient results needed to process the workload. The objects created by users and user applications are called ‘user objects’ while the objects created by SQL Server engine as part of executing/processing the workload are called ‘internal objects’. In this blog, I will focus on user objects and on table variable in particular.

There are three types of user objects; ##table, #table and table variable. Please refer to BOL for specific details. While the difference between ##table (global temporary table) and #table (local temporary table) are well understood, there is a fair amount of confusion between #table and table variable. Let me walk through main differences between these.

A table variable, like any other variable, is a very useful programming construct. The scoping rules of the table variable are similar to any other programming variables. For example, if you define a variable inside a stored procedure, it can’t be accessed outside the stored procedure. Incidentally, #table is very similar. So why did we create table variables? Well, a table variable can be very powerful when user with stored procedures to pass it as input/output parameters (new functionality available starting with SQL Server 2008) or to store the result of a table valued function. Here is a list of all similarities and differences between table variable and #tables

1. The table variable is NOT necessarily memory resident. Under memory pressure, the pages belonging to a table variable can be pushed out to tempdb.

   [caption id="attachment_3895" align="aligncenter" width="650"] Space Usage by Table Variable[/caption]

2. When you create a table variable, it is like a regular DDL operation and its metadata is stored in system catalog. Here is one example to check this
   declare @ttt TABLE(c111 int, c222 int) select name from sys.columns where object_id > 100 and name like 'c%'This will return two rows containing columns c111 and c222. Now this means that if you were encountering DDL contention, you can’t address it by changing a #table to table variable.

3. Transactional and locking semantics. Table variables don’t participate in transactions or locking. Here is one example

   [caption id="attachment_3865" align="aligncenter" width="675"] Checking Locks on Table Variables[/caption]

4. The DML operations (i.e. Insert, Delete, Update) done on table variable are not logged. Here is the example I tried

   [caption id="attachment_3875" align="aligncenter" width="725"] Table Variables and Logging[/caption]

5. No DDL is allowed on table variables. So if you have a large rowset which needs to be queried often, you may want to use #table when possible.

6. No statistics is maintained on table variable which means that any changes in data impacting table variable will not cause recompilation.

thanks

Sunil Agarwal

Comments

• Anonymous
  March 31, 2008
  PingBack from http://manuelsite.freehostingz.com/dmv.html

• Anonymous
  March 31, 2008
  Good article and sample scripts…a few additional points..

1. Queries using table variables don’t generate parallel query plans.  We have found with large temporary datasets it is better to use #tables to take advantage of parallel plans.
2. If you need indexes on a @ table you can work around this by defining CONSTRAINTS on Columns.  Since under the covers a CONSTRAINT is enforced with indexes this will work.  This is required if you need to make updates over linked servers since in 2005 SQL will issues a remote scan across the linked server if columns in JOIN are not indexed. Bert

• Anonymous
  March 31, 2008
  thanks. I will update the blog with the additional info you have provided.

• Anonymous
  April 15, 2008
  There are many myths surrounding table variables and one of the most common is probably the 'in memory'

• Anonymous
  April 29, 2008
  A nice post - I've needed something "microsoft" to point to when I try to explain exactly the points in this post and developers don't believe me < grin >

• Anonymous
  May 22, 2008
  Exactly what I was looking for in understanding the Difference between #table and table variable - Thank You

• Anonymous
  December 16, 2008
  Sunil, I have a question about the 'fourth - the operations done on table variable are not logged' part.  It is true that a name like '%tv_target%' is not showing in the log, but when I looked at all the log records generated after the insert and update operation on table variable, I saw log entries on AllocationUnitName #2F650636, which is the internal name for @tv_target. Just to confirm it, I checked sys catalogs, also compared these entries with logs generated after the same operations on temp tables, and I am convinced they are the logs on table variables. I did this on SQL 2008. Thanks.

• Anonymous
  December 21, 2008
  In the previous blog http://blogs.msdn.com/sqlserverstorageengine/archive/2008/12/21/tempdb-basics.aspx

• Anonymous
  May 17, 2011
  Hi Sunil, Thanks for a detailed explanation on the concept . In the past i was juggling beween cursors and #temptables . This is giving me the same benefit without the fringes the other two has . My precious cpu time and memory will be saved .

• Anonymous
  August 28, 2011
  Hi Sunil,Thanks for this breif but one more question arise which one is better if we talk about performance.Scanerio is like we are using 4 to 5 #temp table in an sp and fetching the all result by union all as per data requirement so plz suugest which one is better.

• Anonymous
  August 29, 2011
  Depends on the size of the data. you can create stats on the #tables for better query plan. Have you looked at the query plan with both choices?  

• Anonymous
  October 18, 2011
  Hello, You say "Under memory pressure, the pages belonging to a table variable can be pushed out to tempdb". I often see that quoted as implying that there is still some memory benefit to table variables vs #temp tables however as far as I can tell the pages belonging to a table variable are always in tempdb, even if it only contains one row DECLARE @T TABLE(X INT); INSERT INTO @T VALUES (1); SELECT sys.fn_PhysLocFormatter(%%physloc%%) FROM @T gives the File/Page/Slot in tempdb used to store the row? Is that a correct understanding? Also can you comment on when these data pages will get persisted to disc? Is it purely when the lazy writer kicks in or is there a per query limit? and is there any reason why table variables would be less likely to have their pages written to disc than #temp tables? It would be nice to have a completely unambiguous statement to point people to that clears this issue up once and for all!

• Anonymous
  December 07, 2011
  Nice Artile with lot of information

• Anonymous
  October 09, 2012
  Adding to YingGuo's comment. Why does SQL Server need to log operations on table variable when it doesn't support transaction rollback for them? I can understand the need in case of global temp tables

• Anonymous
  January 16, 2013
  @Alok It needs to log operations on table variables so that it can perform statement rollback. In my view this article contains a number of inaccuracies and probably just serves to add to the confusion as it comes from an official source. Statement: "The table variable is NOT necessarily memory resident. Under memory pressure, the pages belonging to a table variable can be pushed out to tempdb." My View: Table variables are always in tempdb. The memory issue is orthogonal pages may be any combination of memory only, disc only, both on disc and in memory, Statement:  The operations done on table variable are not logged. My View: Untrue. Shown below USE tempdb; CHECKPOINT; DECLARE @tv_target TABLE (c11 int, c22 char(100)) INSERT INTO @tv_target (c11, c22) VALUES (1, REPLICATE('A',100)),       (2, REPLICATE('A',100)) update @tv_target set c22 = replicate ('B', 100) select * from fn_dblog(null, null) Statement:  Table variables don’t participate in transactions. My View: They don't participate in the user transactions but do participate in system transactions. The logging for which is shown earlier. Statement:  Table variables don’t participate in locking. My View: They do. But there is no need to hold these locks once the statement has completed as they do not participate in any surrounding user transaction. The locking can be seen from below DBCC TRACEOFF(1200,-1) GO DECLARE @tv_target TABLE (c11 int, c22 char(100)) DBCC TRACEON(1200,-1,3604) INSERT INTO @tv_target (c11, c22) VALUES (1, REPLICATE('A',100)), (2, REPLICATE('A',100)) DBCC TRACEOFF(1200,-1) GO

• Anonymous
  May 20, 2016
  That's awesome. Sounds like I've got my solution here! well, while searching for my query i have found another helpful post see here: https://www.linkedin.com/pulse/highlighted-differences-between-sql-server-temporary-tables-andrew