Guaranteed consecutive identity values
in [Programming SQL]
|
Prev: VB Script
Next: Having trouble w/ Decimal field
From: Michael Abraham on 10 Feb 2006 09:48 If I do a INSERT INTO <table> SELECT ... and <table> has an identity column with an increment equal to 1, does SQL/Server 2000 and/or SQL/Server 2005 guarantee that the identity values generated for a single successful INSERT of this type will be consecutive. So if I do an INSERT INTO ... SELECT which inserts, for example, 17 rows, am I guaranteed that the identity values assigned will be N, N+1, N+2, ..., N+16. That is, is it guaranteed that simultaneous INSERTs on other connections will not interrupt the sequence of identity values assigned. Thanks, Mike
From: Dave Frommer on 10 Feb 2006 10:20 No, that is not guaranteed. "Michael Abraham" <mabraham36(a)newsgroup.nospam> wrote in message news:epPcMElLGHA.4064(a)TK2MSFTNGP10.phx.gbl... > If I do a INSERT INTO <table> SELECT ... and <table> has an identity > column with an increment equal to 1, does SQL/Server 2000 and/or > SQL/Server 2005 guarantee that the identity values generated for a single > successful INSERT of this type will be consecutive. So if I do an INSERT > INTO ... SELECT which inserts, for example, 17 rows, am I guaranteed that > the identity values assigned will be N, N+1, N+2, ..., N+16. > > That is, is it guaranteed that simultaneous INSERTs on other connections > will not interrupt the sequence of identity values assigned. > > Thanks, > > Mike >
From: David Portas on 10 Feb 2006 10:22 Michael Abraham wrote: > If I do a INSERT INTO <table> SELECT ... and <table> has an identity column > with an increment equal to 1, does SQL/Server 2000 and/or SQL/Server 2005 > guarantee that the identity values generated for a single successful INSERT > of this type will be consecutive. So if I do an INSERT INTO ... SELECT > which inserts, for example, 17 rows, am I guaranteed that the identity > values assigned will be N, N+1, N+2, ..., N+16. > > That is, is it guaranteed that simultaneous INSERTs on other connections > will not interrupt the sequence of identity values assigned. > > Thanks, > > Mike Not necessarily. For example if you use the IGNORE_DUP_KEY option you'll find that the IDENTITY values for a multiple row insert are not always contiguous. I haven't seen any documentation to the contrary so I suggest that it's safer to assume there may be gaps in the sequence of values generated by a set-based INSERT in any case. Why would it matter? You can't avoid gaps in the sequence of values in an IDENTITY column anyway. To retrieve the IDENTITY values for the inserted rows you should always be able to use an alternate key of the table. -- David Portas, SQL Server MVP Whenever possible please post enough code to reproduce your problem. Including CREATE TABLE and INSERT statements usually helps. State what version of SQL Server you are using and specify the content of any error messages. SQL Server Books Online: http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx --
From: Tibor Karaszi on 10 Feb 2006 10:21 A quick test shows "no": Create the table: create table t(c1 int identity, c2 char(1)) From connection 1 (adjust time in WAITFOR): WAITFOR TIME '16:19:05' INSERT INTO t (c2) SELECT TOP 1000 'a' FROM sysobjects, syscolumns From connection 2 (adjust time in WAITFOR): WAITFOR TIME '16:19:05' INSERT INTO t (c2) SELECT TOP 1000 'b' FROM sysobjects, syscolumns From connection 3 (adjust time in WAITFOR): WAITFOR TIME '16:19:05' INSERT INTO t (c2) SELECT TOP 1000 'c' FROM sysobjects, syscolumns Then check the results. Order by c1 and you will the the "rows interleaved". I tried this in 200 and 2005 with same results. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "Michael Abraham" <mabraham36(a)newsgroup.nospam> wrote in message news:epPcMElLGHA.4064(a)TK2MSFTNGP10.phx.gbl... > If I do a INSERT INTO <table> SELECT ... and <table> has an identity column with an increment > equal to 1, does SQL/Server 2000 and/or SQL/Server 2005 guarantee that the identity values > generated for a single successful INSERT of this type will be consecutive. So if I do an INSERT > INTO ... SELECT which inserts, for example, 17 rows, am I guaranteed that the identity values > assigned will be N, N+1, N+2, ..., N+16. > > That is, is it guaranteed that simultaneous INSERTs on other connections will not interrupt the > sequence of identity values assigned. > > Thanks, > > Mike >
From: Michael Abraham on 10 Feb 2006 11:53
Thanks for all your responses. Specifically addressing David's question, this is a case where I'm working with an existing schema and the table in question does not have an alternate key that I can use to retrieve the actual identity values. So I was hoping that I could rely on consecutiveness to allow me to infer the set of identity values assigned from the last identity value inserted (via SCOPE_IDENTITY) and the row count. I guess it's back to the drawing board. Thanks again for your speedy (and convincingly reasoned) responses. Mike "David Portas" <REMOVE_BEFORE_REPLYING_dportas(a)acm.org> wrote in message news:1139584959.654362.29500(a)g14g2000cwa.googlegroups.com... > Michael Abraham wrote: >> If I do a INSERT INTO <table> SELECT ... and <table> has an identity >> column >> with an increment equal to 1, does SQL/Server 2000 and/or SQL/Server 2005 >> guarantee that the identity values generated for a single successful >> INSERT >> of this type will be consecutive. So if I do an INSERT INTO ... SELECT >> which inserts, for example, 17 rows, am I guaranteed that the identity >> values assigned will be N, N+1, N+2, ..., N+16. >> >> That is, is it guaranteed that simultaneous INSERTs on other connections >> will not interrupt the sequence of identity values assigned. >> >> Thanks, >> >> Mike > > Not necessarily. For example if you use the IGNORE_DUP_KEY option > you'll find that the IDENTITY values for a multiple row insert are not > always contiguous. I haven't seen any documentation to the contrary so > I suggest that it's safer to assume there may be gaps in the sequence > of values generated by a set-based INSERT in any case. > > Why would it matter? You can't avoid gaps in the sequence of values in > an IDENTITY column anyway. To retrieve the IDENTITY values for the > inserted rows you should always be able to use an alternate key of the > table. > > -- > David Portas, SQL Server MVP > > Whenever possible please post enough code to reproduce your problem. > Including CREATE TABLE and INSERT statements usually helps. > State what version of SQL Server you are using and specify the content > of any error messages. > > SQL Server Books Online: > http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx > -- > |