Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In his answer to sql2005 whats better: identity columns or generated uinique id values? mrdenny mentions, says

When SQL Denali comes out it will support sequences which will be more efficient than identity, but you can't create something more efficient yourself.

I'm not so sure. Knowing Oracle sequences, I have either to create a trigger for Insert or to encapsulate each insert into a call of a stored procedure or to pray that I do not forget to properly use the sequence, when I do an ad hoc insert.

I doubt, that the advantages of sequences are so obvious.

share|improve this question
2  
I know it doesn't answer your question but aside from any performance difference, sequences have other advantages. For example a sequence doesn't stop you updating the target column, which is a very inconvenient limitation of IDENTITY. –  sqlvogel Jun 14 '11 at 17:19

2 Answers 2

up vote 28 down vote accepted

I'll answer here as well. It has to do with the internals of how IDENTITY and SEQUENCE work.

With IDENTITY the SQL Server pre-caches 10 values into memory, so that they are readily available. As values are used, a background process generates more values. As you can imagine this 10 value pool can run out pretty quickly, leaving the application at the mercy of the background process that is generating the values.

With SEQUENCE the SQL Server allows you to define how large the cache should be. While the SQL Server doesn't actually keep the values in the cache, it only keeps the current value and the top end value, this will greatly reduce the amount of IO that is needed to create values. Don't set the cache too high, as this will reduce the number of numbers which can be used: if the SQL Server were to crash any values specified in the current cache range which weren't used would be lost.

As for row insertion, just specify a default value for the column, like so:

DEFAULT (NEXT VALUE FOR Audit.EventCounter),
share|improve this answer
3  
+1 This is very interesting and may explain something that was hitherto a bit of a mystery to me. Is there a more optimised path available for multirow inserts do you know? –  Martin Smith Oct 5 '11 at 21:21
    
@MartinSmith can you provide an example of what you are trying to do? With a sequence you just do something like select NEXT VALUE FOR Audit.EventCounter, SomeColumn, AnotherColumn to get values out of the sequence as part of a select which you could then insert into another table easily enough. –  mrdenny Oct 8 '11 at 8:49
    
I'm not trying to do anything! The link in my comment above is to a question on SO asking why IDENTITY performed worse for single row inserts when compared to NEWSEQUENTIALID –  Martin Smith Oct 8 '11 at 8:53
    
Ah, I dodon't know the Internals of newsequentialid, but I would assume that it cached values more like sequence than identity. I'll try and remember to ask the engineers when I'm at the SQL PASS summit this week. –  mrdenny Oct 9 '11 at 19:07
3  
For clarity SQL Server caches 10 identity values, not 20. This is according to Itzik. sqlmag.com/article/sql-server/Sequences-Part-2-129205 –  Aaron Bertrand Oct 20 '11 at 23:01

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.

enter image description here

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 
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.