Do Inserted Records Always Receive Contiguous Identity Values

Question

Consider the following SQL:

CREATE TABLE Foo
(
    ID int IDENTITY(1,1),
    Data nvarchar(max)
)

INSERT INTO Foo (Data)
SELECT TOP 1000 Data
FROM SomeOtherTable
WHERE SomeColumn = @SomeParameter

DECLARE @LastID int
SET @LastID = SCOPE_IDENTITY()

I would like to know if I can depend on the 1000 rows that I inserted into table Foo having contiguous identity values. In order words, if this SQL block produces a @LastID of 2000, can I know for certain that the ID of the first record I inserted was 1001? I am mainly curious about multiple statements inserting records into table Foo concurrently.

I know that I could add a serializable transaction around my insert statement to ensure the behavior that I want, but do I really need to? I'm worried that introducing a serializable transaction will degrade performance, but if SQL Server won't allow other statements to insert into table Foo while this statement is running, then I don't have to worry about it.

score 7 · Accepted Answer · edited Nov 19 '10 at 21:14

7

I disagree with the accepted answer. This can easily be tested and disproved by running the following.

Setup

USE tempdb

CREATE TABLE Foo
(
    ID int IDENTITY(1,1),
    Data nvarchar(max)
)

Connection 1

USE tempdb

SET NOCOUNT ON
WHILE NOT EXISTS(SELECT * FROM master..sysprocesses WHERE context_info = CAST('stop' AS VARBINARY(128) ))
 BEGIN
 INSERT INTO Foo (Data)
 VALUES ('blah')
 END

Connection 2

USE tempdb

SET NOCOUNT ON
SET CONTEXT_INFO 0x

DECLARE @Output TABLE(ID INT)

WHILE 1 = 1
BEGIN
    /*Clear out table variable from previous loop*/
    DELETE FROM  @Output

    /*Insert 1000 records*/
    INSERT INTO Foo (Data)
    OUTPUT inserted.ID INTO @Output
    SELECT TOP 1000 NEWID()
    FROM sys.all_columns

    IF EXISTS(SELECT * FROM @Output HAVING MAX(ID) - MIN(ID) <> 999 )
        BEGIN
        /*Set Context Info so other connection inserting 
          a single record in a loop terminates itself*/
        DECLARE @stop VARBINARY(128) 
        SET @stop = CAST('stop' AS VARBINARY(128))
        SET CONTEXT_INFO @stop

        /*Return results for inspection*/
        SELECT ID, DENSE_RANK() OVER (ORDER BY Grp) AS ContigSection
        FROM 
          (SELECT ID, ID - ROW_NUMBER() OVER (ORDER BY [ID]) AS Grp
           FROM @Output) O
        ORDER BY ID

        RETURN
        END
END

edited Nov 19 '10 at 21:14

gbn

422,506
82
585
676

answered Nov 19 '10 at 17:39

Martin Smith

438,706
87
741
845

Very interesting finding! I appreciate you putting together this test. I even tried to wrap the "Insert 1000 records" statement in a serializable transaction but to no avail, I still get interleaved records. BUT, if I put the "Insert 1000 records" into a serializable transaction AND call SELECT MAX(ID) FROM Foo within the transaction before my insert, I can then guarantee that my records will be contiguous. This solution prevents simultaneous inserts, though, and I'm not willing to take the performance hit that would cause. – John Bledsoe Nov 19 '10 at 21:08
1

@John - serializable on a heap will just acquire an exclusive table lock I think so you could achieve the same effect with a straightforward locking hint. `WITH (TABLOCKX)` As you say this will impact on concurrency though. – Martin Smith Nov 19 '10 at 21:11
my mistake, in the test I was running I added a PK to the ID column on table Foo, since that more closely represented my situation. – John Bledsoe Nov 19 '10 at 21:12
2

Good call. After some research http://sqlblog.com/blogs/paul_white/archive/2010/10/19/sequence-tables.aspx This explains something I heard at a seminar years ago but could never reproduce or find a reference – gbn Nov 19 '10 at 21:13
select max(id) - MIN(id), COUNT(*) from foo the table has got a gapepless series of ids, originating from different sessions. Connection 1 uses no atomic insert nor transaction. So what do you proof? – bernd_k Jul 23 '11 at 17:32

score 6 · Answer 2 · answered Nov 18 '10 at 20:27

6

Yes, they will be contiguous because the INSERT is atomic: complete success or full rollback. It is also performed as a single unit of work: you wont get any "interleaving" with other processes

However (or to put your mind at rest!), consider the OUTPUT clause

DECLARE @KeyStore TABLE (ID int NOT NULL)

INSERT INTO Foo (Data)
OUTPUT INSERTED.ID INTO @KeyStore (ID) --this line
SELECT TOP 1000 Data
FROM SomeOtherTable
WHERE SomeColumn = @SomeParameter

answered Nov 18 '10 at 20:27

gbn

422,506
82
585
676

+1 agreed - inside this transaction, they will be contiguous - but in the lifetime of the table, no guarantees whatsoever are made - there might be gaps – marc_s Nov 18 '10 at 21:32
Sorry, but I ran through @Martin's test and it does indeed show that records can be interleaved into the table in the middle of an insert. I'm sure that your assertion that the INSERT is atomic is still valid, but it seems that doesn't mean that rows can be interleaved. – John Bledsoe Nov 19 '10 at 21:04
As it turns out, even though your answer erred on the contiguous part, I still ended up using OUTPUT ... INTO to solve my problem per your and @KM's suggestion. – John Bledsoe Nov 19 '10 at 21:10
Martin's test doesn't use an atomic insert. He runs some independent inserts in a while loop. – bernd_k Jul 23 '11 at 17:29
1

@bernd_k - Notice that Martin is doing `OUTPUT INSERTED` on the atomic insert of 1000 rows and checking for non-consecutive values. – Nick Chammas Nov 04 '11 at 18:26

score 3 · Answer 3 · answered Nov 18 '10 at 20:28

If you want the Identity values for multiple rows use OUTPUT:

DECLARE @NewIDs table (PKColumn int)
INSERT INTO Foo (Data)
    OUTPUT INSERTED.PKColumn
    INTO @NewIDs
SELECT TOP 1000 Data
FROM SomeOtherTable
WHERE SomeColumn = @SomeParameter

you now have the entire set of values in the @NewIDs table. You can add any columns from the Foo table into the @NewIDs table and insert those columns as well.

score 1 · Answer 4 · answered Nov 18 '10 at 21:24

1

It is not good practice to attach any sort of meaning whatsoever to identity values. You should assume that they are nothing more than integers guaranteed to be unique within the scope of your table.

answered Nov 18 '10 at 21:24

Tim

5,371
3
32
41

I'm inclined to agree with you in theory, but in practice this may not be a problem. Personally I would look for a more robust solution. – MikeAinOz Nov 18 '10 at 21:37

score 0 · Answer 5 · edited Aug 21 '11 at 20:28

0

Try adding the following:

option(maxdop 1)

edited Aug 21 '11 at 20:28

CoolBeans

20,654
10
86
101

answered Mar 09 '11 at 23:20

IHiJump

1

1

Have you run @Martin's test on a multi-core machine with this option set? I'd be interested to hear the results. – John Bledsoe Mar 10 '11 at 13:12

Do Inserted Records Always Receive Contiguous Identity Values

5 Answers5

Linked