Since the Itzik Ben Gan article was written the hardcoded cache size of 10 for IDENTITY seems to have been changed. From the comments on this connect item
The size of the pre-allocation is based on the size of the data type
of the column the identity property is defined on. For a SQL Server
integer column, the server pre-allocates identities in ranges of 1000
values. For the bigint data type the server pre-allocates in ranges of
10000 values.
The article here tests various sequence cache sizes and insert batch sizes and comes up with the following results.
Which appears to show that for large inserts IDENTITY out performs SEQUENCE. It doesn't test cache size 1,000 however and also those results are just one test. Looking specifically at cache size 1,000 with various batch sizes of inserts I got the following results.
Try 1
+---------+--------------+---------+
| NumRows | SequenceTime | IdTime |
+---------+--------------+---------+
| 10 | 9636 | 9182 |
| 100 | 4272 | 4000 |
| 1000 | 6545 | 6818 |
| 10000 | 25637 | 22819 |
| 100000 | 264924 | 172646 |
| 1000000 | 2373136 | 2014024 |
+---------+--------------+---------+
Try 2
+---------+--------------+---------+
| NumRows | SequenceTime | IdTime |
+---------+--------------+---------+
| 10 | 5091 | 4363 |
| 100 | 4363 | 4363 |
| 1000 | 6545 | 6818 |
| 10000 | 25819 | 23455 |
| 100000 | 213012 | 175100 |
| 1000000 | 2330678 | 2028934 |
+---------+--------------+---------+
Try 3
+---------+--------------+---------+
| NumRows | SequenceTime | IdTime |
+---------+--------------+---------+
| 10 | 10091 | 7909 |
| 100 | 4091 | 4272 |
| 1000 | 6727 | 6909 |
| 10000 | 25092 | 23637 |
| 100000 | 216558 | 175373 |
| 1000000 | 2412410 | 2020933 |
+---------+--------------+---------+
For the smaller batch sizes the figures are a bit variable but for 10000+ the IDENTITY version was always faster.
Script to reproduce
CREATE SEQUENCE dbo.Seq1_cache_1000
AS INT
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
NO CYCLE
CACHE 1000
CREATE TABLE dbo.t1_identity (
id INT IDENTITY(1,1) NOT NULL
,c1 INT NOT NULL
,CONSTRAINT PK_t1_identity PRIMARY KEY CLUSTERED (id)
);
CREATE TABLE dbo.t1_Seq1_cache_1000 (
id INT DEFAULT NEXT VALUE FOR dbo.Seq1_cache_1000 NOT NULL
,c1 INT NOT NULL
,CONSTRAINT PK_t1_Seq1_cache_1000 PRIMARY KEY CLUSTERED (id)
);
GO
CREATE FUNCTION [dbo].[TallyTable]
(
@N INT
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
(
WITH
E1(N) AS
(
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
) -- 1*10^1 or 10 rows
, E2(N) AS (SELECT 1 FROM E1 a, E1 b) -- 1*10^2 or 100 rows
, E4(N) AS (SELECT 1 FROM E2 a, E2 b) -- 1*10^4 or 10,000 rows
, E8(N) AS (SELECT 1 FROM E4 a, E4 b) -- 1*10^8 or 100,000,000 rows
SELECT TOP (@N) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N
FROM E8
)
GO
DECLARE @Results TABLE(
BatchCounter INT,
NumRows INT,
SequenceTime BIGINT,
IdTime BIGINT)
DECLARE @NumRows INT = 10,
@BatchCounter INT
WHILE @NumRows <= 1000000
BEGIN
SET @BatchCounter = 0
WHILE @BatchCounter <= 10
BEGIN
--Do inserts using Sequence
DECLARE @SequenceTimeStart DATETIME2(7) = SYSUTCDATETIME()
INSERT INTO dbo.t1_Seq1_cache_1000
(c1)
SELECT N
FROM [dbo].[TallyTable] (@NumRows)
OPTION (RECOMPILE)
DECLARE @SequenceTimeEnd DATETIME2(7) = SYSUTCDATETIME()
--Do inserts using IDENTITY
DECLARE @IdTimeStart DATETIME2(7) = SYSUTCDATETIME()
INSERT INTO dbo.t1_identity
(c1)
SELECT N
FROM [dbo].[TallyTable] (@NumRows)
OPTION (RECOMPILE)
DECLARE @IdTimeEnd DATETIME2(7) = SYSUTCDATETIME()
INSERT INTO @Results
SELECT @BatchCounter,
@NumRows,
DATEDIFF(MICROSECOND, @SequenceTimeStart, @SequenceTimeEnd) AS SequenceTime,
DATEDIFF(MICROSECOND, @IdTimeStart, @IdTimeEnd) AS IdTime
TRUNCATE TABLE dbo.t1_identity
TRUNCATE TABLE dbo.t1_Seq1_cache_1000
SET @BatchCounter +=1;
END
SET @NumRows *= 10;
END
SELECT NumRows,
AVG(SequenceTime) AS SequenceTime,
AVG(IdTime) AS IdTime
FROM @Results
GROUP BY NumRows
