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?