Difference between temporary tables and table variables


This article outlines the differences between temporary tables and table variables; such as statistics and visibility. Understanding these difference will be helpful to users to write efficient query and deciding whether to use temporary table or table variable.

This article outlines the differences between temporary tables and table variables; such as statistics and visibility. Understanding these difference will be helpful to users to write efficient query and deciding whether to use temporary table or table variable.

One of the big difference between temporary tables and table variables is that statistics are not created/updated on table variables. Due to this Query Optimizer uses a fixed value for the number of rows in a table variable and not the actual number of rows; adding or removing rows from table variable do not update this fixed value. As Query Optimizer has no reliable statistics for table variable, this could lead to a bad query execution plan.

Other difference between temporary tables and table variables is that temporary tables are visible in the created routine and also in the child routines; while table variables are only visible in the created routine.

Indexes can not be created on table variables but are allowed for temporary tables. However you can create constraints on the table variables.

Schema modifications are possible on temporary tables but not on table variables.


Temporary Tables versus Table Variables


Item Temporary Tables Table Variables
Statistics YES NO
Indexes YES Only Constraints
Schema modification YES NO
Available in child routines YES NO
In Memory structure NO NO


Comments

Author: sudhanshu pal24 Jul 2014 Member Level: Silver   Points : 10

a

Author: naveensanagasetti24 Jul 2014 Member Level: Gold   Points : 3

Hi,

Nice and simple Explanation, but you should give some detailed explanation out of this like which one is best and why..? With sample screenshots then it will be easy to understood to everyone.

As of my knowledge TableVarriables are best option.

Why because it will destroy once the execution part has been completed. But That's not possible in Temporary variables we need to destroy that object through manually...

Author: praveen28 Jul 2014 Member Level: Silver   Points : 0

A Temporary table is also destroyed automatically when session that created temporary table ends.

Author: naveensanagasetti28 Jul 2014 Member Level: Gold   Points : 3

Yah, but assume that In my procedure I want to return 1,00,00,000 records. I just store those all records into one temporary table, until I destroy this temporary table object will hold some data, once we allocate data to that temp table. Until and unless that is waste of memory. Once Procedure ends that object will destroy but up to that it holds that data, this will definitely causes performance issue.



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: