sql - Is Order Guaranteed When Inserting Multiple Rows with Identity?

Question

When inserting multiple rows in a table, is there any guarantee that they go in in the order I specify? For instance, take the following:

DECLARE @blah TABLE
(
    ID INT IDENTITY(1, 1),
    Name VARCHAR(100) NOT NULL
);

INSERT INTO @blah (Name)
    VALUES('Timmy'),
    ('Jonny'),
    ('Sally');

SELECT * FROM @blah

Is there any guarantee that Sally will have a higher primary key than Timmy?

Answer 1

The very similar question was asked before.

You can specify an ORDER BY in the INSERT.

If you do that, the order in which the IDENTITY values are generated is guaranteed to match the specified ORDER BY in the INSERT.

Using your example:

DECLARE @blah TABLE
(
    ID INT IDENTITY(1, 1) NOT NULL,
    Name VARCHAR(100) NOT NULL
);

INSERT INTO @blah (Name)
SELECT T.Name
FROM
    (
        VALUES
        ('Timmy'),
        ('Jonny'),
        ('Sally')
    ) AS T(Name)
ORDER BY T.Name;

SELECT
    T.ID
    ,T.Name
FROM @blah AS T
ORDER BY T.ID;

The result is:

+----+-------+
| ID | Name  |
+----+-------+
|  1 | Jonny |
|  2 | Sally |
|  3 | Timmy |
+----+-------+

That is, Name have been sorted and IDs have been generated according to this order. It is guaranteed that Jonny will have the lowest ID, Timmy will have the highest ID, Sally will have ID between them. There may be gaps between the generated ID values, but their relative order is guaranteed.

If you don't specify ORDER BY in INSERT, then resulting IDENTITY IDs can be generated in a different order.

Mind you, there is no guarantee for the actual physical order of rows in the table even with ORDER BY in INSERT, the only guarantee is the generated IDs.

In a question INSERT INTO as SELECT with ORDER BY Umachandar Jayachandran from MS said:

The only guarantee is that the identity values will be generated based on the ORDER BY clause. But there is no guarantee for the order of insertion of the rows into the table.

And he gave a link to Ordering guarantees in SQL Server, where Conor Cunningham from SQL Server Engine Team says:

4. INSERT queries that use SELECT with ORDER BY to populate rows guarantees how identity values are computed but not the order in which the rows are inserted

There is a link to MS knowledge base article in the comments in that post: The behavior of the IDENTITY function when used with SELECT INTO or INSERT .. SELECT queries that contain an ORDER BY clause, which explains it in more details. It says:

If you want the IDENTITY values to be assigned in a sequential fashion that follows the ordering in the ORDER BY clause, create a table that contains a column with the IDENTITY property and then run an INSERT ... SELECT ... ORDER BY query to populate this table.

I would consider this KB article as an official documentation and consider this behaviour guaranteed.