Karthick P.K on SQL Server

SQL Server: Table Variables (VS) Temp Tables

Posted by Karthick P.K on January 15, 2009

Table Variables (VS) Temp Tables

SQLServer2005 caches temp tables and temp variables only under some conditions.
Scenarios where temp table/variable are not cached (see below) may cause performance degradation as compared to SQLServer2000.

Following are scenarios where temp table/variable are not cached:
1. select into #t
2. alter table #t
3. create index on #t
4. Global temp tables (##t)
5. Local temp tables on adhoc level (nest level 0)
6. table variables are also not cached for dynamic SQL.

What are some of the drawbacks of table variables?

These are some of the drawbacks as compared to temporary tables:

Table variables do not maintain statistics like temporary tables can. Statistics cannot be created on table variables through automatic creation or by using the CREATE STATISTICS statement.

Therefore, for complex queries on large tables, the lack of statistics may deter the optimizer to determine the best plan for a query, thus affecting the performance of that query.

The table definition cannot be changed after the initial DECLARE statement.
Tables variables cannot be used in a INSERT EXEC or SELECT INTO statement.
CHECK constraints, DEFAULT values, and computed columns in the table type declaration cannot call user-defined functions.
You cannot use the EXEC statement or the sp_executesql stored procedure to run a dynamic SQL Server query that refers a table variable, if the table variable was
created outside the EXEC statement or the sp_executesql stored procedure because table variables can be referenced in their local scope only, an EXEC statement and
a sp_executesql stored procedure would be outside the scope of the table variable.

However, you can create the table variable and perform all processing inside the EXEC statement or the sp_executesql stored procedure because then the table
variables local scope is in the EXEC statement or the sp_executesql stored procedure.

Are table variables memory-only structures that are assured better performance as compared to temporary or permanent tables, because temporary or permanent tables
are maintained in a database that resides on the physical disk and also logged?

A table variable is not a memory-only structure. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store
data. Table variables are created in the tempdb database similar to temporary tables. If memory is available, both table variables and temporary tables are
created and processed while in memory (data cache).

Do I have to use table variables instead of temporary tables?

The answer depends on these three factors:
The number of rows that are inserted to the table and are they joined with other tables.
The number of recompilations the query is saved from.
The type of queries and their dependency on indexes and statistics for performance.


Option recompile can help optimizer to estimate the number of rows table variable  refer 


If you liked this post, do like us on Facebook at and join our Facebook group!/groups/454762937884205/

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

8 Responses to “SQL Server: Table Variables (VS) Temp Tables”

  1. ambien said

    Your current write-up has proven helpful to myself. It’s very useful and you are clearly extremely well-informed in this region.
    You possess exposed my personal face to be able to numerous thoughts about this kind of matter together with intriquing, notable and solid content material.

  2. select into #t is always cached.

  3. Excellent confident synthetic vision with regard
    to detail and can foresee issues before these people occur.

  4. It’s hard to come by experienced people in this particular subject,
    however, you seem like you know what you’re talking about!

  5. Great blog here! Additionally your website loads up very fast!
    What host are you the usage of? Can I get your associate hyperlink on your host?

    I desire my web site loaded up as quickly as yours lol

  6. leticia said

    Thanks for visiting,
    I’m Leticia Figuero.
    If you’ve ever been too busy and couldn’t finish a research assignment , then you’ve come to the right place. I assist students in all areas of the writing process . I can also write the essay from start to finish.
    My career as a professional writer started during high school. After learning that I was very capable in the field of academic writing, I decided to take it up as a job .

    Talented Academic Writer- Leticia Figuero- Company

  7. leonard said

    Hi Everybody
    Thanks for checking out my about page. My name is Leonard.
    I have worked since high school in this niche. My aptitude for writing started at a young age. I wrote poetry as a child and eventually went on to work with my school newspaper.
    This early tryst into journalism eventually led me to academic writing. There is plenty of work for qualified writers. I specialize in research papers , but have the skills to do all types of academic writing.
    Reach out for more information about rates and a price quote. I’m looking forward to helping you.

    Academic Writer – Leonard – Company

Leave a Reply to leonard Cancel reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: