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.
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.
> 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.
> 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.
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
> 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.
> 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.
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.
"Michael Abraham" <mabraha...@newsgroup.nospam> wrote in message
> 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.
|
