2

When using a Table Value Constructor (http://msdn.microsoft.com/en-us/library/dd776382(v=sql.100).aspx) to insert multiple rows, is the order of any identity column populated guaranteed to match the rows in the TVC?

E.g.

CREATE TABLE A (a int identity(1, 1), b int)

INSERT INTO A(b) VALUES (1), (2)

Are the values of a guaranteed by the engine to be assigned in the same order as b, i.e. in this case so they match a=1, b=1 and a=2, b=2.

Simon D
  • 4,150
  • 5
  • 39
  • 47

2 Answers2

2

Piggybacking on my comment above, and knowing that the behavior of an insert / select+order by will guarantee generation of identity order (#4: from this blog)

You can use the table value constructor in the following fashion to accomplish your goal (not sure if this satisfies your other constraints) assuming you wanted your identity generation to be based on category id.

insert into thetable(CategoryId, CategoryName)
select *
from
  (values
    (101, 'Bikes'),
    (103, 'Clothes'),
    (102, 'Accessories')
  ) AS Category(CategoryID, CategoryName)
order by CategoryId
greyalien007
  • 510
  • 4
  • 13
  • So using the OP's example, if the OP wanted to guarantee a match between `a` and `b` in each row, they could go like `INSERT INTO A(b) SELECT b FROM (VALUES (1), (2)) AS v (b) ORDER BY b`. Is that what you are saying? – Andriy M Jul 17 '14 at 07:57
  • Note you can only guarantee the order of fetching rows by using `order by`. If you are inserting from a select (without `order by`) there's no guarantee how that rows are inserted (in any particular order) from start. Using a expression like in the example above don't makes garantees unless you explicit include some column to be used in ther order by and using ordered values. In the example above you ill get 101,102 and 103 as the `order by` commanded – jean Jan 04 '16 at 09:48
-2

It depends as long as your inserting the records in one shot . For example after inserting if you delete the record where a=2 and then again re insert the value b=2 ,then identity column's value will be the max(a)+1

To demonstrate 

 DECLARE @Sample TABLE
 (a int identity(1, 1), b int)

 Insert into @Sample values (1),(2)

a   b
1   1
2   2

 Delete from @Sample where a=2

 Insert into @Sample values (2)
 Select * from @Sample

 a  b
 1  1
 3  2
praveen
  • 12,083
  • 1
  • 41
  • 49
  • I am inserting in one shot, I want to know if the order is guaranteed. Compare http://blogs.msdn.com/b/sqltips/archive/2005/07/20/441053.aspx item 4 which has order guarantees for inserts. After my insert above I want to know if it's possible for the order to be a=1, b=2 and a=2, b=1 – Simon D Jun 27 '12 at 07:11
  • The combination will always remain same .The documentation says that when you use Insert into ...Select from the row getting inserted may not be in the correct order .The data is inserted with correct value but not in the correct order .For example if u insert b=1 and b=2 .The data retrieved will be a=2 b=2 and a=1 b=1.This issue has been resolved from sql server 2008 – praveen Jun 27 '12 at 08:24
  • I know the TVC is a new feature in 2008, but not sure what you are referring to with the issue. If you can point me to some documentation or anything else that says that this is guaranteed order, will mark this as the correct answer. Thanks. – Simon D Jun 27 '12 at 11:33
  • I too have been recently been looking for this information, but can't find it. The sql-92 standard (and we know how t-sql complies 100% to this :P) requires that the table value constructor combination functions like union all. Union all does not guarantee order, so I'm inclined to believe at this moment that it is not guaranteed. – greyalien007 Jun 18 '14 at 03:55
  • 3
    The demonstration has nothing to do with the question asked. – ypercubeᵀᴹ Jul 17 '14 at 09:00