|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Guaranteed consecutive identity valuesIf 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 No, that is not guaranteed.
Show quote "Michael Abraham" <mabraham36@newsgroup.nospam> wrote in message news:epPcMElLGHA.4064@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 > 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. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "Michael Abraham" <mabraham36@newsgroup.nospam> wrote in message news:epPcMElLGHA.4064@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 > Michael Abraham wrote:
> If I do a INSERT INTO <table> SELECT ... and <table> has an identity column Not necessarily. For example if you use the IGNORE_DUP_KEY option> 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 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 -- 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 Show quote "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message news:1139584959.654362.29500@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 > -- > If you are on 2005, you can perhaps use the new OUTPUT option for the INSERT command.
-- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "Michael Abraham" <mabraham36@newsgroup.nospam> wrote in message news:O7y$LKmLGHA.2628@TK2MSFTNGP15.phx.gbl... > 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_dpor***@acm.org> wrote in message > news:1139584959.654362.29500@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 >> -- >> > > Then the schema is malformed. With only one exception, a surrogate (or
IDENTITY column) should never be the only candidate key for a permanent table. The exception deals with abstract data and is beyond the scope of a single newsgroup post, but the underlying rule is that if any column in a table contains information that originates from outside of the database or is used to enforce a database constraint, then if there is a surrogate, there must also be an additional candidate key--even if it includes every column but the surrogate. Show quote "Michael Abraham" <mabraham36@newsgroup.nospam> wrote in message news:O7y$LKmLGHA.2628@TK2MSFTNGP15.phx.gbl... > 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_dpor***@acm.org> wrote in message > news:1139584959.654362.29500@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 >> -- >> > >  |
|||||||||||||||||||||||
Other interesting topics