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
The very similar question was asked before.
You can specify an
ORDER BY in the
If you do that, the order in which the
IDENTITY values are generated is guaranteed to match the specified
ORDER BY in the
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 | +----+-------+
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:
- 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
IDENTITYvalues to be assigned in a sequential fashion that follows the ordering in the
ORDER BYclause, create a table that contains a column with the
IDENTITYproperty and then run an
INSERT ... SELECT ... ORDER BYquery to populate this table.
I would consider this KB article as an official documentation and consider this behaviour guaranteed.