table variables and indexes

MSSQLTips has an article comparing table variables and temporary tables and their performance.  The article is interesting for the conclusion and for the methodology (use profiler).

The findings are that for large data sets, temp tables are better.  For smaller data, not much difference.  However there is a difference because you can put a non-clustered index on a temp table and create statistics, so when columns involving the index are involved 1) selects on the temp table are faster and 2) updates, deletes and inserts can be SLOWER – due to the second action needed to maintain the index entry.

These tests were run using SQL Profiler and the trace output had the text of the query, and columns for CPU, Reads, Writes and duration.  The columns displayed (in some cases such as select only text and CPU were deemed relevant).

Advertisements