Friday, June 17, 2016

Three ways of Inserting empty values into a table with Single Identity Column

I know, that is very unusual situation when you need to fill out a table with only one Identity Column.
However, when you need it, you need to do it quickly and efficiently.

At first, will create a table:
CREATE TABLE tbl_Single_Identity(ID INT IDENTITY(1,1) 
 CONSTRAINT PK_ID PRIMARY KEY WITH (FILLFACTOR=100));


Here is how you would do this:

Approach #1


INSERT INTO tbl_Single_Identity DEFAULT VALUES;
That is easy and fast, but what would you do if you need to insert more values than one?
Clicking on F5 again and again?

Approach #2

INSERT INTO tbl_Single_Identity DEFAULT VALUES;
GO 1000
That is quick and dirty way to insert 1000 records, but what if you need to insert million or more?
That "GO" will be too slow. So, approach #3 comes.

Approach #3

Third approach is to load all values in bulk and I currently see two pretty easy ways of doing it:

A. Adding extra column, fill it in and then delete it.


ALTER TABLE tbl_Single_Identity ADD Dummy TINYINT NOT NULL;
GO
;WITH
  Pass0 as (select 1 as C union all select 1),
  Pass1 as (select 1 as C from Pass0 as A, Pass0 as B),
  Pass2 as (select 1 as C from Pass1 as A, Pass1 as B),
  Pass3 as (select 1 as C from Pass2 as A, Pass2 as B),
  Pass4 as (select 1 as C from Pass3 as A, Pass3 as B),
  Pass5 as (select 1 as C from Pass4 as A, Pass4 as B)
INSERT INTO tbl_Single_Identity(Dummy)
SELECT TOP (1000000) C FROM Pass5;
GO
ALTER TABLE tbl_Single_Identity DROP COLUMN Dummy;
GO
ALTER INDEX PK_ID ON tbl_Single_Identity REBUILD WITH (FILLFACTOR=100);
GO

I do not think it is the most effective way because requires extra space for a column and then for index rebuilt to gain that space back.

B. Allowing insertion into the Identity column
SET IDENTITY_INSERT tbl_Single_Identity ON;
GO
;WITH
  Pass0 as (select 1 as C union all select 1),
  Pass1 as (select 1 as C from Pass0 as A, Pass0 as B),
  Pass2 as (select 1 as C from Pass1 as A, Pass1 as B),
  Pass3 as (select 1 as C from Pass2 as A, Pass2 as B),
  Pass4 as (select 1 as C from Pass3 as A, Pass3 as B),
  Pass5 as (select 1 as C from Pass4 as A, Pass4 as B)
INSERT INTO tbl_Single_Identity(ID)
SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM Pass5;
GO
SET IDENTITY_INSERT tbl_Single_Identity OFF;
GO

Both Inserts A and B have the very same result and produce the same size data, but surprisingly, even though Approach 3.A. looks uglier and requires Index Rebuild it works much faster than Approach 3.B. because of Window function "ROW_NUMBER" and associated with it spill in tempDB for Sorting operation.

No comments:

Post a Comment