microsoft.public.sqlserver.programming

Re: Guaranteed consecutive identity values

David Portas <remove_before_replying_dpor...@acm.org>

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
--