CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Pharylon
Pharylon

Reputation: 9916

Is Order Guaranteed When Inserting Multiple Rows with Identity?

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?

Upvotes: 12

Views: 5075

Answers (2)

Vladimir Baranov
Vladimir Baranov

Reputation: 32685

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:

  1. 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.

Upvotes: 15

Tom H
Tom H

Reputation: 47444

Your best two options are:

Process the rows individually - insert the parent row, get the ID, then insert the children, then insert the next parent row, etc.

Alternatively, assuming that the data has an actual identifier in the real world (for example, email address, SSN, etc.) then you can use that to join back to your parent table when inserting into the child table.

Upvotes: 0

Related Questions