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

Kimberly Tripp, President of Founder of SQLSkills.com, 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).

Reference:   http://www.sqlskills.com/blogs/kimberly/PermaLink.aspx?guid=8be9d388-b354-429f-8c98-e45989a4bebe