Are auto incrementing identities always given in the order of inserts in SQL Server?

Question

If I create a simple table like this:

CREATE TABLE SimpleTable (
     [Id] INT IDENTITY(1,1) NOT NULL,
     [Value] VARCHAR(10) NOT NULL,
     CONSTRAINT [PK_Email] PRIMARY KEY CLUSTERED 
     (
         [Id] ASC
     ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

And then I insert into the table 100 times, within the same transaction, like this...

TRUNCATE TABLE SimpleTable -- ensure reset identity seed

BEGIN TRAN

INSERT INTO SimpleTable(Value) VALUES('First')
INSERT INTO SimpleTable(Value) VALUES('Second')
INSERT INTO SimpleTable(Value) VALUES('Third')
INSERT INTO SimpleTable(Value) VALUES('Fourth')
/* ... etc */
INSERT INTO SimpleTable(Value) VALUES('Hundredth')

COMMIT TRAN

Is there any guarantee that the inserted rows will be given identity values in the order in which the insert statements are executed?

More specifically, if I run this query after the inserts...

SELECT * FROM SimpleTable ORDER BY 1

Will I always see this...

Id    Value
----------------------------------------------
1     First
2     Second
3     Third
4     Fourth
... etc
100   Hundredth

Or, does SQL not guarantee this?

Answer 1

If this is the only transaction on that table, then yes. However, another transaction could also do an insert at the same time, and be somewhere in between 1-100, unless you use SERIALIZABLE isolation.

From IDENTITY (Property) (Transact-SQL) in the product documentation:

The identity property on a column does not guarantee the following:

• Uniqueness of the value – Uniqueness must be enforced by using a PRIMARY KEY or UNIQUE constraint or UNIQUE index.

• Consecutive values within a transaction – A transaction inserting multiple rows is not guaranteed to get consecutive values for the rows because other concurrent inserts might occur on the table. If values must be consecutive then the transaction should use an exclusive lock on the table or use the SERIALIZABLE isolation level.

• Consecutive values after server restart or other failures –SQL Server might cache identity values for performance reasons and some of the assigned values can be lost during a database failure or server restart. This can result in gaps in the identity value upon insert. If gaps are not acceptable then the application should use its own mechanism to generate key values. Using a sequence generator with the NOCACHE option can limit the gaps to transactions that are never committed.

• Reuse of values – For a given identity property with specific seed/increment, the identity values are not reused by the engine. If a particular insert statement fails or if the insert statement is rolled back then the consumed identity values are lost and will not be generated again. This can result in gaps when the subsequent identity values are generated

Answer 2

There's no guarantee unless you hold an exclusive lock on the table until all inserts commit or set your isolation level to SERIALIZABLE. That can cause serious performance and scalability problems with your app so don't do it. Also no guarantees if you do bulk insert.

If you can provide your functional requirements instead of a very narrowly defined sample, we can probably help you find a viable solution.

Answer 3

There is not guarantee if you execute this code in parallel with other transactions.

You have the option to use OUTPUT INSERTED.ColumnId to get the current inserted row id:

--This is your real table, for this purpouses I used a TABLE variable
DECLARE @TheRealTable TABLE(
    [Id]            [int] IDENTITY(1, 1) NOT NULL,
    [Name]          [varchar](50) NOT NULL,
    [Comments]  [varchar](60) NULL
)

DECLARE @tmpTable TABLE (
    [Id]            [int] NOT NULL,
    [Name]          [varchar](50) NOT NULL,
    [Comments]  [varchar](60) NULL
)


INSERT INTO @TheRealTable
           OUTPUT INSERTED.Id
           ,INSERTED.Name
           ,INSERTED.Comments INTO @tmpTable
     VALUES
     (
        'TestName1'
        ,'TestComments1')

INSERT INTO @TheRealTable
           OUTPUT INSERTED.Id
           ,INSERTED.Name
           ,INSERTED.Comments INTO @tmpTable
     VALUES
     (
        'TestName2'
        ,'TestComments2')

INSERT INTO @TheRealTable
           OUTPUT INSERTED.Id
           ,INSERTED.Name
           ,INSERTED.Comments INTO @tmpTable
     VALUES
     (
        'TestName3'
        ,'TestComments3')

INSERT INTO @TheRealTable
           OUTPUT INSERTED.Id
           ,INSERTED.Name
           ,INSERTED.Comments INTO @tmpTable
     VALUES
     (
        'TestName4'
        ,'TestComments4')

INSERT INTO @TheRealTable
           OUTPUT INSERTED.Id
           ,INSERTED.Name
           ,INSERTED.Comments INTO @tmpTable
     VALUES
     (
        'TestName5'
        ,'TestComments5')


SELECT * FROM @tmpTable