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
a