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.

Answer 1

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 2

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