Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.
Loading

1st Prize - Apple iPad


SQLServer Quiz 2011 - What is the difference between - Table Variable and Temp Tables

  • For all practical purposes, let us take SQL Server 2008 version as the standard.

    Why to use Temp Table Vs Table Variable is not yet understood by many. Let this discussion bring out some of the basics and internals of how these two function internally.

    • Discuss the scenarios when you will use one over the other
    • Any resource considerations (CPU, Memory) that differentiate the use
    • To any of the assumptions made, would be great if you can give examples or DMVs to prove your point.
    Posted on 03-01-2011 00:00 |
    vinodk_sql
    465 · 0% · 84

20  Answers  

Subscribe to Notifications
Previous 1 | 2 Next
  • Score
    9

    Assuming that we are limiting our discussion scope to local temporary tables and table variables, I will not comment on specifics of global temporary tables, table-valued parameters, or table expressions. These other temporary table derivatives would have similar categories of consideration as the following. I have structured my foundation from the concepts found in chapter 2 of Inside Microsoft SQL Server 2005: T-SQL Programming.

    Temp-ology

    (study of temporary data objects :) )

    Scope

    Temporary tables have a scoping flexibility over table variables in that their scope goes across batches and into inner levels (e.g. stored procs, dynamic batches, triggers) as the table is scoped to the current session. In contrast, the table variable has a scope that is limited to the current batch. Consider the following script:

    use tempdb;
    go
    
    -----------
    -- Setup --
    -----------
    if exists(select * from sys.objects where object_id = object_id('dbo.myProc1') and type in ('P', 'PC'))
        drop procedure myProc1;
    go
    create procedure myProc1 as
        select col1 as t_batch2 from #tmp1;
    go
    
    ------------------------------
    -- Temp Table Scope Batch 1 --
    ------------------------------
    create table #tmp1 (col1 int);
    insert into #tmp1 values(1);
    
    select col1 as t_batch1 from #tmp1;
    go
    
    ------------------------------
    -- Temp Table Scope Batch 2 --
    ------------------------------
    exec myProc1;
    select col1 as [t_batch2.1] from #tmp1;
    go
    
    -------------
    -- Cleanup --
    -------------
    if exists(select * from sys.objects where object_id = object_id('dbo.myProc1') and type in ('P', 'PC'))
        drop procedure myProc1;
    go
    if (object_id('tempdb..#tmp1') is not null)
        drop table #tmp1;
    go
    
    ----------------------------
    -- Table Variable Batch 1 --
    ----------------------------
    declare @tmp1 table (col1 int)
    insert into @tmp1 values(1);
    
    select col1 as v_batch1 from @tmp1;
    go
    
    ----------------------------
    -- Table Variable Batch 2 --
    ----------------------------
    select col1 as v_batch2 from @tmp1;
    go
    

    If you run the whole script, you will get 4 results and one big, fat error, "Must declare the table variable "@tmp1"." After the setup, the first batch clearly shows it can select on the temporary table. Furthermore, the batch shows that the temporary table is still available in a new batch and even accessible to a stored procedure, even though it was completely unaware of it when the procedure was created. In contrast, the table variable can be select from in the context of the first batch, but the second batch errors because the table variable has fallen out of scope for the batch. I did not provide a procedure to demonstrate the table variable out of scope as it will not compile. The best way to remember this is to remember a table variable is just that: a variable; its scope will behave just like that of any other variable.

    Schema Changes & Adornments

    Because temporary tables largely behave like a standard user table, it is able to do a majority of the typical DDL tricks like adding & modifying columns, constraints, indexes (it should be noted that triggers and foreign keys don't work on temporary tables). However, when we try some of these same tricks on table variables, they are simply not allowed. For example, you cannot alter the schema of a table variable after its declaration. This means adding/removing columns and/or changing data types. You are allowed a primary key and unique keys; in fact, it will politely refuse any indexing that isn't a unique key.

    Statistics & Recompiles

    Between the two, only the temporary table receive statistics on their data distribution that can be used for query optimization. As such, this can become problematic with large table variables in as much as the optimizer may likely choose a suboptimal plan that can cause excessive IO. Check out the following example:

    use tempdb
    go
    
    ------------
    -- Set up --
    ------------
    set statistics io on;
    go
    declare @t table (col1 int not null primary key, col2 int, filler char(256) not null default('a'), unique (col2, col1));
    create table #t (col1 int not null primary key, col2 int, filler char(256) not null default('a'), unique (col2, col1));
    
    with num(n) as
    (
        select 1 n union all
        select n+1 from num where n < 100000
    )
    insert into @t
    (col1, col2)
    select
        n
        ,n
    from num
    option(maxrecursion 0);
    
    with num(n) as
    (
        select 1 n union all
        select n+1 from num where n < 100000
    )
    insert into #t
    (col1, col2)
    select
        n
        ,n
    from num
    option(maxrecursion 0);
    
    -----------------------------------
    -- SELECT Statements to Evaluate --
    -----------------------------------
    select * from @t where col1 = 1;
    select * from @t where col1 <= 5000;
    select * from @t where col2 = 1;
    select * from @t where col2 <= 2;
    
    select * from #t where col1 = 1;
    select * from #t where col1 <= 5000;
    select * from #t where col2 = 1;
    select * from #t where col2 <= 2;
    
    --------------
    -- Clean Up --
    --------------
    drop table #t;
    

    In studying the output of the IO statistics, one will note that this scenario demonstrates the excessive reads in of the 3 & 4th SELECT of the table variable when compared to its equivalent on the temporary table. This can be further supported by looking at the query plans of each of the queries; you will note that the table variable plans estimate only 1 row returning - again, due to the lack of statistical information of the data.

    In turn, the availability of statistics to the temporary table make it much more susceptible to recompiles. The table variable does not suffer such potential CPU & cache woes.

    Outer Transaction Participation

    Table variables don't behave like a permanent or temporary table when participating in a transaction. Consider the following:

    use tempdb
    go
    
    ------------
    -- Set up --
    ------------
    declare @t table (col1 int);
    create table #t (col1 int)
    
    ------------------------------
    -- Who has data in the end? --
    ------------------------------
    begin tran
        insert into @t values(1);
        insert into #t values(1);
    rollback
    
    select * from @t
    select * from #t
    
    -- Clean Up --
    drop table #t
    

    In running this batch, you will find that the table variable retains the inserted row whereas the temporary table will not. It should be noted that if the insert had failed in some fashion, the statement would rollback (there would not be a partial insert). Another feature of this transactional behavior in a table variable is that it does not suffer from locking as the temporary table would.

    Theory's Great, But What to Do in Practice?

    I don't think I can give a great set of guidelines on whether to use a temporary table or a table variable that will guarantee the best outcome. Instead, I try to consider the consequences of either and then move forward. For example, if scoping becomes an issue for the task at hand, perhaps you require the flexibility of the temporary table, even if the data set is only a couple dozen rows. Another might be that I'm wanting to pass table data into a stored procedure; the new table-valued parameter (ya, it's like a read-only table variable based on a custom table type) makes for a nice development pattern and doesn't require me to have temporary (or even permanent) tables that I have to clean up afterward. One last example might be when coding a trigger, the table variable would likely provide the best flexibility because of its blindness to the outer transaction, thus not being impacted by rollback statements.

    In summary, it's important to weight the costs of which to use for the task at hand. Using only one of the two would be like trying to use a wrench to tighten bolts and drive in nails; it might work for both tasks, but will definitely suck at one of them.

    Replied on Mar 1 2011 6:16AM  . 
    Scott Epperly
    161 · 1% · 301
  • Score
    8

    Discuss the scenarios when you will use one over the other

    I would use a table variable over a temp table if at least one of the following statements is true:

    • a temp table is not allowed, e.g. inside a UDF for intermediate storage of result set needed for further processing inside that UDF (msdn ref 1) or as a parameter for a stored procedure (msdn ref 2)
    • the DDL need to include DB specific objects (e.g. user defined data type or xml schema collection) (sqlservercentral.com ref 1)
    • I need to capture data modified within a transaction after ROLLBACK (sqlservercentral.com ref 2)
    • the number of rows will be very low (msdn ref 3)
      • Remark: I'm not providing a specific number here, since the tipping point should be verified by testing each solution based on a given scenario, but definitely lower than a few thousand rows...
      • I usually compare the execution plan for both version: if they are (almost) identical under each tested condition and the performance is at least as good as with the temp table solution I might use the table variable.
      • It's worth to mention not to trust the query cost when dealing with table variables and the actual number of rows is significantly higher than the estimated number (= 1). It is recommended to rely on either SET STATISTICS TIME ON or using Profiler.
    • I'm faced with a scenario where a temp table in a stored procedure would cause recompiles with significant performance impact and I cannot use the KEEP PLAN or KEEPFIXED PLAN query hint and the temp table solution would show a better performance during comparison (sqlserverplanet.com ref 1)
    • I would benefit from less locking and logging resources required for a table variable. (sqlservercentral.com ref 3)
    • the scope is limited to the current batch (e.g. the table will not be used in a nested stored procedure)

    The best summary for comparing temp table and table variables I know of is Wayne Sheffields article "Comparing Table Variables with Temporary Tables"

    Any resource considerations (CPU, Memory) that differentiate the use

    I do not use resource considerations to differentiate the use, since the resources required are an effect and not a root cause.

    For example, a table variable with a large number of rows used in a join might lead to an execution plan that will be less optimal than with a temp table since it might not use parallelism (due to the estimated number of just one row for the temp table). The effect would be a heavy CPU usage of a single CPU (depending on the settings).

    Especially since virtualization will play an increasing role, there will be less standard tools we can trust (e.g. perfmon might not return correct results for the CPU usage of a virtual machine -> mikedipetrillo.com)

    Edit: formatting modified.

    Replied on Mar 1 2011 4:08PM  . 
    lmu92
    0 · 0% · 0
  • Score
    4

    1.Table variables doesn't have Non-Clustered Indexes 2.No constraints possible in table variables 3.You are not able to create default values on table variable columns 4.You are not able to create statistics against table variables

    Replied on Mar 1 2011 10:19PM  . 
    mithila
    2376 · 0% · 5
  • Score
    5
    • both are need for the storage & manipulation of temporal data.
      • table variables were introduced with SQL Server 2000 and were designed for returning datasets from table-valued functions
      • both instantiated in tempdb and are both backed by physical disk
      • Changes to both temp tables and table variables are logged in the transaction log
      • temp tables can be altered with DDL statements but table variables can't (cannot create a nonclustered index on a table variable for example).That makes every table variable a heap, or at best a table with a single, clustered index, and every table variable access a table scan
      • Temp tables have a looser scope than table variables
      • the table variable change is committed implicitly after each DML statement (Transactions involving table variables last only for the duration of the DML statement)
      • transactions on temp tables can span multiple DML statements and require less locking
      • SQL Server creates statistics for temp tables, so the query optimiser can choose different plans for data involving temp tables.
      • SQL Server does not create statistics on columns in table variables
    Replied on Mar 2 2011 12:25AM  . 
    indika saminda kannangara
    188 · 1% · 251
  • Score
    6

    Discuss the sceanrios when you will use one over the other:

    It is not something predefined but rather the set of different factors and their weights are making the difference:

    IT DEPENDS (not always) Factors:

    1. Quantity of rows: low - Table Variable : High - Temporary table
    2. Going to be used in Joins, Self-Joins: No - Table Variable : Yes - Temporary table
    3. Going to be used in queries with WHERE statement: No - Table Variable : Yes - Temporary table
    4. Going to be used in single batch: Yes - Table Variable : No - Temporary table

    YES/NO Factors:

    1. You would like the optimizer to use parallelism: No - Table Variable | Yes - Temporary table
    2. You need to have ability to roll back changes in temp data: No - Table Variable | Yes - Temporary table
    3. You need to alter table with DDL statements: No - Table Variable | Yes - Temporary table
    4. You need to use table as read only Input/Output Parameter for a stored procedure: Yes - Table Variable | No - Temporary table
    5. To be used to store a result of the table valued function: Yes - Table Variable

    And of course - no statistics on Table Variable.

    Any resource consideration (CPU, Memory) that differentiate the use:

    CPU - Table variable performs better because no locking process is triggered

    I/O - More I/O is with Temporary tables in theory (but because no query optimization on Table Variable - you code may get better I/O with Temporary Table),

    Both CPU and I/O - no data for rollback is maintained for Table Variable. Metadata for Temporary table is stored in system catalog. Those consideration give an advantage with CPU and I/O to Table Variable.

    RAM - the same (both can be in TempDB, both can be in memory only, both can be partially in memory and on the disk). and sys.dm_db_session_space_usage can be used to verify this (that is little bit about DMV).

    Replied on Mar 2 2011 1:37AM  . 
    Igor Zakharov
    162 · 1% · 300
  • Score
    4

    Hello!

    For smaller data sets, which are not being manipulated (but only used as reference or lookups), I would prefer table variables over temporary tables.

    First and foremost, at the end of the day, a table variable is, a variable. Hence, whenever SQL Server generates the query plan, it optimizes the plan as if the table variable has only one value/record! Hence, table variables should not be used when dealing with large data sets - temporary tables are the best bet in this case. This is also the reason why table variables do not generate parallel execution plans if the query modifies these "tables" and why they cause fewer recompilations of stored procedures when compared to temporary tables.

    Next, because the table variable is a variable, it's scope is limited - and are automatically cleaned up. While the automatic cleanup is great, one cannot pass these around across object boundaries (eg. across stored procedures or functions - unless if a table UDF is used).

    Finally, here's the shocker - both use tempDB, and have similar I/O requirements! I used the DMV - sys.dmdbfilespaceusage - to confirm this (I will be writing about this on my blog after this particular question of the quiz closes).

    Summarizing, table variables are great options if one needs small temporary storage for a very short period of time - it's like a small shoebox container - one can use it to carry small things around, but definitely not the heavy-weight lifting. One does need to call in the experts (temporary tables) for that.

    Until we meet next time,

    Be courteous. Drive responsibly.

    http://beyondrelational.com/blogs/nakul/default.aspx

    Replied on Mar 2 2011 1:38AM  . 
    Nakul Vachhrajani
    4 · 36% · 11635
  • Score
    3

    Hi all,

    The differences between table variable and temporary table are 1. We cannot have transaction in table variables. 2. The Procedure containing temporary tables are not pre-compiled. 3. We cannot create non-clustered index on table variables. 4. Constraints cannot be created on table variables. 5. I would recommend using table variables for a small data set required for a small period of time.

    Replied on Mar 2 2011 3:43AM  . 
    Rishabh
    188 · 1% · 251
  • Score
    4

    Hello,

    Based on my experience I am giving feedback on “Temp table and Table Variable”

    1. Temp Table (Global or Local) -

      a. When large data set to be stored.

      b. To take advantage of indexes

      c. Join multiple times.

      d. Store result from a stored procedure.

      e. Process data in .net CLR (sending result to CLR procedure)

      f. Dealing with Transaction

      g. share data among different session.

      h. To process data in dynamic SQL.

      i. Quickly create result from a SQL

    2. Table Variable –

      a. Store small result

      b. return result from a function.

      c. To avoid locking operation

    Replied on Mar 2 2011 6:51AM  . 
    nilesh.argade
    1537 · 0% · 13
  • Score
    7

    There is no any thumb rule to decide whether to use Table Variable or Temporary Table. It depends upon the requirement of the application and different situations.

    Following are some of the facts for Table Variable and Temporary Table:

    (1) Once the structure of a Table Variable is defined with DECLARE statement, it can not be altered. The structure of a Temporary Table can be altered once it is defined with CREATE TABLE statement.

    (2) We can not create any index explicitly on Table Variables. Only indexes for Primary Key and Unique Key can be available on Table Variables. On the other hand, indexes can be created on Temporary Tables.

    (3) Transactions and related statements like ROLLBACK TRANSACTION can not affect Table Variables. That is to say, if some DML operations are performed on a Table Variable within a transaction followed by the execution of ROLLBACK TRANSACTION, then the DML operations performed on Table Variable will not be rolled back. Transactions do affect the temporary tables and DML operations performed on Temporary Table can either be committed or rolled back.

    (4) STATISTICS are not maintained for Table Variables. STATISTICS can not be created on Table Variable with CREATE STATISTICS statement. While, STATISTICS can be maintained and created for Temporary Tables.

    (5) Table Variable can not be used as target table in SELECT…INTO statement. Temporary Table can be used as target table in SELECT…INTO statement.

    (6) Table Variable declared outside dynamic SQL statement is not accessible in dynamic SQL statements executed either by spexecutesql or EXECUTE statement. Temporary Table declared outside dynamic SQL statement is accessible in dynamic SQL statements executed either by spexecutesql or EXECUTE statement.

    (7) Table Variable is not accessible to a nested child stored procedure. A Temporary Table is accessible to a nested child stored procedure.

    There is one common thing between Table Variable and Temporary Table is that they both use TempDB database. There is a kind of understanding amongst the developers that Table Variable is an in-memory object and thus is faster than Temporary Table. This is not entirely true. If the data which is to be stored and processed in a Table Variable is large and can not fit into memory, then Table Variable may use tempdb database.

    To prove this point, we can consider following example. In this example, we are creating a table with 5 million records. Then, a stored procedure is created which fetches data from this table and inserts this data into Table Variable followed by a simple SELECT statement on this Table Variable.

    CREATE TABLE tblData
    (
         ID INT IDENTITY(1,1)
         ,SomeData VARCHAR(10)
    )
    GO
    
    --Insert 5 Million records
    INSERT INTO tblData(SomeData) VALUES (CHAR(RAND()*100))
    GO 5000000
    

    Now, we are creating following stored procedure

    CREATE PROCEDURE spTestTableVar
    AS 
    BEGIN
        DECLARE @TempTable TABLE
        (	
        	ID INT 
        	,SomeData VARCHAR(10)		
        )
    
    INSERT INTO @TempTable 
    SELECT ID,SomeData FROM tblData
    
    SELECT * FROM @TempTable
    END
    GO
    

    When you call this stored procedure, during the execution of stored procedure, execute the following SELECT statement in another query window:

    SELECT * FROM sys.dm_tran_locks
    SELECT * FROM sys.dm_db_file_space_usage
    

    In result set of query sys.dmtranlocks, you will notice that there are few exclusive locks at page level for resourcedatabaseid=2 by the session that executed the stored procedure. To identify the session, look for column requestsessionid. The resourcedatabaseid=2 means tempdb database.

    The DMV sys.dmdbfilespaceusage gives page allocation details for tempdb database. During the execution of above stored procedure, you will notice that the value for column userobjectreservedpagecount is updated which reveals the total number of pages allocated to user objects.

    This means that Table Variable stores its data in tempdb database.

    I have also created the “Temporary Table” version of this stored procedure as shown below:

    CREATE PROCEDURE spTestTableTemp 
     AS 
        BEGIN
            CREATE TABLE #TempTable
            (	
            	ID INT 
            	,SomeData VARCHAR(10)
                 )
    
        INSERT INTO #TempTable
        SELECT ID,SomeData FROM tblData	
    
        SELECT * FROM #TempTable
    END
    

    While playing around these stored procedures, I have noticed that if a clustered index is created on ID column of #TempTable before inserting data, then it boosts up performance a little bit! I compared the executions of both of these stored procedures and looked upon the counts for CPU, Duration, Read and Write in SQL Profiler but did not find much difference except of a few milliseconds. This may be because this is a simple example. However, in case stored procedure is going to be lengthy one and the data you are going to store in either Table Variable or Temporary Table is going to be large and queried frequently on the basis of by filtering few columns upon which the indexes are desirable then Temporary Tables can outperform Table Variables. Because, on Table Variable we can not have indexes other than for Primary Key and Unique Key. This means that we can not have index on Table Variable on non-unique columns. So, data retrievals from large datasets which essentially require helpful indexes can be poor in case of Table Variables. On the contrary, we can create Clustered/Nonclustered indexes on required columns on Temporary Tables. Also, STATISTICS are maintained for Temporary Tables which can be used efficiently by query optimizer in order to generate better execution plans for the queries.

    However, there is one benefit of using Table Variables over Temporary Table is that Table Variables cause fewer re-compilations than Temporary Tables because they are treated as variables and not as objects; thus not requiring the re-resolution phase that Temporary Tables require. As Temporary Tables are objects themselves, stored procedures that make use of Temporary Tables tend to be re-complied frequently due to re-resolution phase for non-existing objects (Temporary Tables) in order to generate execution plans. These frequent re-compilations can degrade and hurt the query performance.

    Looking at the list of restrictions/limitations of Table Variables as compared to Temporary Tables, one has so many reasons to use Temporary Tables instead of Table Variables if the cost of re-compilations is not higher than the cost of poor data retrievals with Table Variables due to the lack of appropriate indexes and statistics on large data. To sum up the things, one can come on rough conclusion that Table Variables are suitable for small datasets while Temporary Tables are suitable for large datasets. However, to any problem solution, it is always desirable to implement the solution with these two different scenarios; either with Table Variable or Temporary Table and make the decision by comparing the performance and the required functionalities achieved by each solution.

    Replied on Mar 2 2011 9:20AM  . 
    Bihag Thaker
    829 · 0% · 35
  • Score
    5

    Hi,

    My take on temp table / table variables

    Advantage Table Variable

    1. Data usage is small.
    2. Capture rollback transaction data for table (Only way).
    3. Can be passed to SP (input /output parameter), Function table valued function.
    4. Alternative to keep fixed plan option|(SP Recompile) with temp table usage where small data change/usage cause sp recompile because of small threshold of criteria
    5. Single thread with minimal/No Locking.
    6. Mostly usage in memory untill spill over to tempdb because of large data/Volume.

    Advantage Temp Table

    1. More efficient in large data as we can use (create index, Alter table, constraints) like fix tables. Staistics are build on temp tables.
    2. Scope is more wide..used in child sp.
    3. created on fly to capture data (Select * from into )
    4. Transactional consistency.
    5. can be used in creation of Dynamic SQL with exec,sp_executesql (can't use table variable).
    6. Can take advantage of parrallel processing (Multipul schedulars).

    Any resource consideration (CPU, Memory) that differentiate the use

    1. CPU - Temp table (Multi thread), Table Variable (Single thread).
    2. IO - More in temp table as compare to table variable (mostly in memory).
    3. Locking - Less locking in table variable.
    Replied on Mar 3 2011 12:56AM  . 
    Neeraj mittal
    387 · 0% · 105
Previous 1 | 2 Next

Your Answer


Sign Up or Login to post an answer.
Please note that your answer will not be considered as part of the contest because the competition is over. You can still post an answer to share your knowledge with the community.

Copyright © Rivera Informatic Private Ltd.