Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 248
Pharylon Avatar asked Jan 05 '16 21:01

Pharylon


People also ask

Does SQL maintain order of insertion?

The answer for this simple case is: "Yes". Rows are inserted in the provided order in the VALUES expression. And if your id column is a serial type, values from the underlying sequence will be fetched in that order.

Can we insert a row for identity column?

The system generates an IDENTITY column value when the keyword DEFAULT is used as the insert_clause for the IDENTITY column.

Can we insert multiple rows single insert statement?

INSERT-SELECT-UNION query to insert multiple records Thus, we can use INSERT-SELECT-UNION query to insert data into multiple rows of the table. The SQL UNION query helps to select all the data that has been enclosed by the SELECT query through the INSERT statement.

Can you insert into multiple rows?

Yes, instead of inserting each row in a separate INSERT statement, you can actually insert multiple rows in a single statement. To do this, you can list the values for each row separated by commas, following the VALUES clause of the statement.


1 Answers

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.

like image 140
Vladimir Baranov Avatar answered Nov 16 '22 01:11

Vladimir Baranov