Performance Comparison – Tables, Temp Tables, and Table Variables

People are confused about the differences amoung user tables, temp tables, and table variables in terms of inserting performance. I did a test today on my laptop – Core I7 Q720, 8GB RAM, SSD. I have both tempdb and user db on the same hard drive. I tested following scenarios over permanent tables, permanent tables in tempdb, global temp tables, local temp tables and table variables.

  1. Heap: inserting records to a heap one by one, each insert is an auto commit operation
  2. Primary Key: clustered primary key over an identity column. each insert is an auto commit operation
  3. Heap – Transaction: all insertions are in one transaction for each batch, table is a heap
  4. PKĀ – Transaction: all insertions are in one transaction for each batch, table has clustered primary key on an identity column.
  5. Heap BulkLoad: Bulk load data to a heap
  6. PKĀ BulkLoad: Bulk load data to a table with clustered index on an identity column

Continue reading “Performance Comparison – Tables, Temp Tables, and Table Variables”