SQL SERVER – Comparison : Similarity and Difference #TempTable vs @TempVariable

#TempTable and @TempVariable are different things with different scope. Their purpose is different but highly overlapping. TempTables are originated for the storage and & storage & manipulation of temporal data. TempVariables are originated (SQL Server 2000 and onwards only) for returning date-sets from table-valued functions.

Common properties of #TempTable and @TempVariable
They are instantiated in tempdb.
They are backed by physical disk.
Changes to them are logged in the transaction log1. However, since tempdb always uses the simple recovery model, those transaction log records only last until the next tempdb checkpoint, at which time the tempdb log is truncated.

Discussion of #TempTable and @TempVariable relations with each other.
A table variable behaves like a local variable. It has a well-defined scope, which is the function, stored procedure, or batch it is declared in. Within its scope, a table variable can be used like a regular table. Table variables are automatically cleaned up at the end of the function, stored procedure, or batch in which they are defined. Table variables require fewer locking and logging resources. If a temporary tables are used in stored procedure, it will create separate copy of the temporary table for each user in system who makes use of that stored procedure. SQL Server identifies the different temporary tables by internally adding a numerical suffix to the name.

Table variables used in stored procedures cause fewer recompilations of the stored procedures than when temporary tables are used. If the temporary tables referred to in a stored procedure are created outside the procedure, that will cause recompilation. Recompilation occurs when DECLARE CURSOR statements whose SELECT statements reference a temporary table, or DROP TABLE statements comes before other statements referencing a temporary table. In each of these cases, changing to a table variable rather than a temporary table will avoid the repeated recompilation.

Temp Tables supports non-clustered indexes and creates statistics on the query executed. This helps some query which needs stats and indexes to run faster. Temp Tables supports input or output parameters. Also they can be copied to another temp tables. Temp tables supports SELECT INTO or INSERT EXEC but Temp variables does not. Temp Table definition can be changed after it is created. Temp table can be explicitly dropped as well.

My Recommendation: Which one to use?
Simple : Choose a TempVariables as Default. If any of the following circumstances arises use TempTables.
1) Transactions needs to rollback
2) If query optimizer needs stats to run complex query
3) If result sets of one tables needed for another stored procedure like SELECT INTO or INSERT EXEC
4) Complex logic of dynamic SQL, which are not supported by TableVariables like creating Index or Constraints
5) Results set is very large (greater than 100K rows)

The bottom line is that both temporary tables are table variables are invaluable tools in your SQL Server toolbox, and you really should become familiar with both.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

13 thoughts on “SQL SERVER – Comparison : Similarity and Difference #TempTable vs @TempVariable

  1. You have stated that both temp table and table variable instantiated in tempdb. But I remember reading that table variables are stored in memory. Could you please confirm.

    Like

  2. Shikanth,

    If you read what you have found and it is clearly stated what I have said and it confirms it. They still perform I/O on tempdb.

    “A table variable is created in memory, and so performs slightly better than #temp tables (also because there is even less locking and logging in a table variable). A table variable might still perform I/O to tempdb (which is where the performance issues of #temp tables make themselves apparent), though the documentation is not very explicit about this. “

    Like

  3. Very good site listing
    I agree with you on all but one comment about using temp tables.
    Use temp tables if:
    Results set is very large (greater than 100K rows)

    I think it is efficient to use staging physical tables if result set is very large. because of sp recompilation, locks on tempdb and better use of parallelism

    Like

  4. Hi,

    I have a table with columns
    distributor_id ,
    brand_id,
    year,
    month,
    total_sales

    distributor_id ,
    brand_id,
    year,
    month are my primary keys.

    I have records for last 3 years od data.

    I have distinct records till last year,
    Some are till last month (Current month – 1)
    some are till feb 2007…….
    some are till jan 2007..

    Now i want to insert records to all distinct
    distributor_id ,
    brand_id,
    year,
    month
    Zero for sales

    where i have to take max month + 1 and hardcode as zero till current month + 10 months.

    i appreciate if you get me a insert script without cursors.

    Like

  5. Pingback: SQL SERVER - SQL SERVER - Comparison : Similarity and Difference #TempTable vs @TempVariable - Part 2 Journey to SQL Authority with Pinal Dave

  6. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  7. Dave,

    We have a procedure that is ran evry 2 minutes in a windows service. The procedure uses a table variable. It appears tha after a couple hours of execution the server that this service is running on starts to take a performance hit. We have also seen a tremendous increase in timeouts since the implementation of this stored procedure. Is ti possible that the table variable is not being released from memeory?

    Like

  8. Hi Readers,
    Please consider the following situation ,
    Here part (1) will render the result but not part (2) and (4)
    Can any one suggest an answer, why the temp table and table variable canot be used in string level execution.
    In part (2) it will execute, but while fetching the value (select * from #tmp_date) it will generate error,
    but part(3) will render the result(select * from ##tmp_date).

    (1) Table
    select convert(varchar,getdate(),101) as ToDay into dat_date
    select * from dat_date
    drop table dat_date

    (2)Temporary table
    declare @var varchar(1000)
    set @var=’select convert(varchar,getdate(),101) as ToDay into #tmp_date’
    execute (@var)
    select * from #tmp_date
    drop table #tmp_date

    (3)Temporary table
    declare @var varchar(1000)
    set @var=’select convert(varchar,getdate(),101) as ToDay into ##tmp_date’
    execute (@var)
    select * from ##tmp_date
    drop table ##tmp_date

    (4)Table variable
    declare @var_ varchar(1000)
    declare @var_table table(To_day datetime)
    set @var_=’insert into @var_table select convert(varchar,getdate(),101) as ToDay ‘
    execute (@var_)
    select * from @var_table
    drop table @var_table

    Any guesses?? please post…!

    Like

    • And the following will be working………..!!

      declare @var_ varchar(1000)
      declare @var_table table(To_day datetime)
      set @var_=’select convert(varchar,getdate(),101) as ToDay ‘
      insert into @var_table
      execute (@var_)
      select * from @var_table

      Like

  9. Hi Pinal Dave,

    I’m ur big fan. Thanks for all your articles.

    1.Please tell me what are the issues in using temp tables.
    2.For handling huge records which one will be better. Temp tables or physical tables with unique id to identify the session. If physical tables please justify?

    Thanks,
    Dineshbabu

    Like

  10. Pingback: SQL SERVER – Weekly Series – Memory Lane – #036 | Journey to SQL Authority with Pinal Dave

  11. Hi Dave,
    Could u plz explain or give me the link where I can find the answer of the below question:
    ” why Table variables used in stored procedures cause fewer recompilations of the stored procedures than when temporary tables are used?”

    Like

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