Feb 032012
 

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 »

Jan 302012
 

Global temp tables can be accessed by one or more sessions. This important feature of SQL Server becomes more important than it was in my programming live since more applications I worked with and am currently working on have parallel processing involved heavily. Very often, amount of data generated from one sessioin are shared to many concurrent sessions. Global temp tables come and play. Creating a global temp table is simple.

create table ##temp(i int)

It will be released when

  1. It’s explicitly removed by any sessions
  2. The creator session closed and there is no other sessions referencing this session.

Continue reading »