TempDB:: Table variable vs local temporary table

TempDB:: Table variable vs local temporary table

Rate This
  • Comments 15

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 the user objects commonly referred to as temporary tables (#, ##) and table variables. While the differences 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 used 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 are some similartities and differences between the two:

·         First, the table variable is NOT necessarily memory resident. Under memory pressure, the pages belonging to a table variable can be pushed out to tempdb. Here is one example shows space taken by table variable in tempdb

use tempdb

go

 

drop table #tv_source

go

 

create table #tv_source(c1 int, c2 char(8000))

go

 

declare @i int

select @i = 0

while (@i < 1000)

begin

       insert into #tv_source values (@i, replicate ('a', 100))

       select @i = @i + 1

end

 

DECLARE @tv_target TABLE (c11 int, c22 char(8000))

 

 

 INSERT INTO @tv_target (c11, c22)

    SELECT c1, c2

    FROM  #tv_source

 

-- checking the size through DMV.

-- The sizes here are in 8k pages. This shows the allocated space

-- to user objects to be 2000 pages (1000 pages for #tv_source and

-- 1000 pages for @tv_target

 

Select total_size = SUM (unallocated_extent_page_count) +

SUM (user_object_reserved_page_count) +

SUM (internal_object_reserved_page_count) +

SUM (version_store_reserved_page_count) +

SUM (mixed_extent_page_count),

 

SUM (unallocated_extent_page_count) as freespace_pgs,

SUM (user_object_reserved_page_count) as user_obj_pgs,

SUM (internal_object_reserved_page_count) as internal_obj_pgs,

SUM (version_store_reserved_page_count)  as version_store_pgs,

SUM (mixed_extent_page_count) as mixed_extent_pgs

from sys.dm_db_file_space_usage

·         Second, 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 cannot address it by changing a #table to table variable.

·         Third, transactional and locking semantics. Table variables don’t participate in transactions or locking. Here is one example


-- create a source table

create table tv_source(c1 int, c2 char(100))

go

 

declare @i int

select @i = 0

while (@i < 100)

begin

       insert into tv_source values (@i, replicate ('a', 100))

       select @i = @i + 1

       end

-- using #table

create table #tv_target (c11 int, c22 char(100))

go

 

BEGIN TRAN

 

    INSERT INTO #tv_target (c11, c22)

            SELECT c1, c2

            FROM  tv_source

 

 

-- using table variable

 

DECLARE @tv_target TABLE (c11 int, c22 char(100))

 

BEGIN TRAN

       INSERT INTO @tv_target (c11, c22)

        SELECT c1, c2

    FROM  tv_source

 

 

-- Now if I look at the locks, you will see that only

-- #table takes locks. Here is the query that used

-- to check the locks   

select 

    t1.request_session_id as spid, 

    t1.resource_type as type,  

    t1.resource_database_id as dbid, 

    (case resource_type

      WHEN 'OBJECT' then object_name(t1.resource_associated_entity_id)

      WHEN 'DATABASE' then ' '

      ELSE (select object_name(object_id) 

            from sys.partitions 

            where hobt_id=resource_associated_entity_id)

    END) as objname, 

    t1.resource_description as description,  

    t1.request_mode as mode, 

    t1.request_status as status,

       t2.blocking_session_id

from sys.dm_tran_locks as t1 left outer join sys.dm_os_waiting_tasks as t2

ON t1.lock_owner_address = t2.resource_address

 

Another interesting aspect is that if I rollback the transaction involving the table variable earlier, the data in the table variable is not rolled back.

Rollback

-- this query will return 100 for table variable but 0 for #table.

SELECT COUNT(*) FROM @tv_target

·         Fourth, the operations done on table variable are not logged. Here is the example I tried

-- create a table variable, insert bunch of rows and update

DECLARE @tv_target TABLE (c11 int, c22 char(100))

 

INSERT INTO @tv_target (c11, c22)

    SELECT c1, c2

    FROM  tv_source

 

 

-- update all the rows

update @tv_target set c22 = replicate ('b', 100)

 

 

-- look at the top 10 log records. I get no records for this case

select top 10 operation,context, [log record fixed length], [log record length], AllocUnitId, AllocUnitName

from fn_dblog(null, null)

where AllocUnitName like '%tv_target%'

order by [Log Record Length] Desc

 

-- create a local temptable

drop table #tv_target

go

 

create table #tv_target (c11 int, c22 char(100))

go

 

INSERT INTO #tv_target (c11, c22)

    SELECT c1, c2

    FROM  tv_source

 

-- update all the rows

update #tv_target set c22 = replicate ('b', 100)

 

 

-- look at the log records. Here I get 100 log records for update

select  operation,context, [log record fixed length], [log record length], AllocUnitName

from fn_dblog(null, null)

where AllocUnitName like '%tv_target%'

order by [Log Record Length] Desc

 

·         Fifth, 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 so that you can create appropriate indexes. You can get around this by creating unique constraints when declaring table variable.

 

·         Finally, no statistics is maintained on table variable which means that any changes in data impacting table variable will not cause recompilation of queries accessing table variable.

 

 

Thanks

Sunil

 

Leave a Comment
  • Please add 2 and 1 and type the answer here:
  • Post
  • PingBack from http://manuelsite.freehostingz.com/dmv.html

  • 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

  • thanks. I will update the blog with the additional info you have provided.

  • There are many myths surrounding table variables and one of the most common is probably the 'in memory'

  • 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 >

  • Exactly what I was looking for in understanding the Difference between #table and table variable - Thank You

  • 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.

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

  • 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 .

  • 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.

  • 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?  

  • 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!

  • Nice Artile with lot of information

  • 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

  • @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

Page 1 of 1 (15 items)