SQL Server 2011 - SEQUENCE Part II

Following my first post of the new Sequence object in Denali, and possibly promoted by the disturbingly  "cursor-like" syntax, I decided to see how the feature performs, like for like, against the Identity column property.

To do this, I altered my original sequence by using the following syntax...

ALTER SEQUENCE Sales.SalesOrderNumbers 
--AS INT   
MINVALUE 1  
NO MAXVALUE   
RESTART WITH 1
INCREMENT BY 1

...I then created a control table called dbo.Control, that contains one column, called col1, which holds the number 1 through 1000000. I then created 2 tables with the following code...

CREATE TABLE Sales.SalesOrdersSequence
(
EntID INT,
OrderNumber INT DEFAULT  (NEXT VALUE FOR Sales.SalesOrderNumbers)
)
CREATE TABLE Sales.SalesOrdersIdentityCol
(
EntID INT,
OrderNumber INT IDENTITY(1,1)
)

...Next, to ensure a fair test, I tore down the procedure and buffer caches with the following statements...

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

...and in Options, I turned on the options to record Time statistics. I then ran the following INSERT statement, and included the actual execution plan...

INSERT INTO Sales.SalesOrdersIdentityCol (EntID)
SELECT col1 FROM Controltbl

...I returned the following statistics and query plan...

 SQL Server Execution Times:
   CPU time = 6536 ms,  elapsed time = 17100 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

...I then tore down the caches once again, before running the following INSERT statement against the table using the sequence object...

INSERT INTO Sales.SalesOrdersSequence (EntID)
SELECT col1 FROM Controltbl

...Although the execution plan was (unsurprisingly) identical, the following stats were returned...

 SQL Server Execution Times:
   CPU time = 4758 ms,  elapsed time = 10098 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

...Frankly, I was very pleasantly surprised by the results. The second INSERT took only 59% of the time, and caused only 73% of the CPU overhead. This means that replacing your IDENTITY Columns with references to a sequence object could have a positive effect on Bulk Inserts.

Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom