SYSK 32: Should you use temp tables or table variables in SQL?

Kimberly Tripp, President of Founder of, uses the following “rule-of-thumb”:

Use temp tables when:
• The object is used over a longer period of time
• You will create non-key indexes on it to improve performance
• You want the ease of creating the temp table provided by the SELECT INTO statement
• You need the ability to use the temporary table in nested sub-procedures (since temp tables are not local to the procedure in which it was created)

Use table variable when:
• You have smaller objects
• It doesn’t need to be accessed outside of the procedure in which it was created
• When you only need KEY indexes (a table variable only supports the indexes created by a create table statement – meaning PRIMARY KEY and UNIQUE KEY).


Comments (0)