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” →