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.
- Heap: inserting records to a heap one by one, each insert is an auto commit operation
- Primary Key: clustered primary key over an identity column. each insert is an auto commit operation
- Heap – Transaction: all insertions are in one transaction for each batch, table is a heap
- PK – Transaction: all insertions are in one transaction for each batch, table has clustered primary key on an identity column.
- Heap BulkLoad: Bulk load data to a heap
- PK BulkLoad: Bulk load data to a table with clustered index on an identity column