Do Inserted Records Always Receive Contiguous Iden

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.

Answer 1

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.

Answer 2

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

Answer 3

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

Answer 4

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.

Answer 5

Try adding the following:

option(maxdop 1)