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

when inserting multiple rows in table, there guarantee go in in order specify? instance, take following:

declare @blah table (     id int identity(1, 1),     name varchar(100) not null );  insert @blah (name)     values('timmy'),     ('jonny'),     ('sally');  select * @blah 

is there guarantee sally have higher primary key timmy?

the similar question asked before.

you can specify order by in insert.

if that, order in identity values generated guaranteed match specified order by in insert.

using example:

declare @blah table (     id int identity(1, 1) not null,     name varchar(100) not null );  insert @blah (name) select t.name     (         values         ('timmy'),         ('jonny'),         ('sally')     ) t(name) order t.name;  select     t.id     ,t.name @blah t order t.id; 

the result is:

+----+-------+ | id | name  | +----+-------+ |  1 | jonny | |  2 | sally | |  3 | timmy | +----+-------+ 

that is, name have been sorted , ids have been generated according order. guaranteed jonny have lowest id, timmy have highest id, sally have id between them. there may gaps between generated id values, relative order guaranteed.

if don't specify order by in insert, resulting identity ids can generated in different order.

mind you, there no guarantee actual physical order of rows in table order by in insert, guarantee generated ids.

in question insert select order by umachandar jayachandran ms said:

the guarantee identity values generated based on order clause. there no guarantee order of insertion of rows table.

and gave link ordering guarantees in sql server, conor cunningham sql server engine team says:

4. insert queries use select order populate rows guarantees how identity values computed not order in rows inserted

there link ms knowledge base article in comments in post: the behavior of identity function when used select or insert .. select queries contain order clause, explains in more details. says:

if want identity values assigned in sequential fashion follows ordering in order by clause, create table contains column identity property , run insert ... select ... order by query populate table.

i consider kb article official documentation , consider behaviour guaranteed.