Guaranteed consecutive identity values - microsoft.public.sqlserver.programming

Message from discussion Guaranteed consecutive identity values
Path: g2news1.google.com!postnews.google.com!g14g2000cwa.googlegroups.com!not-for-mail
From: "David Portas" <REMOVE_BEFORE_REPLYING_dpor...@acm.org>
Newsgroups: microsoft.public.sqlserver.programming
Subject: Re: Guaranteed consecutive identity values
Date: 10 Feb 2006 07:22:39 -0800
Organization: http://groups.google.com
Lines: 38
Message-ID: <1139584959.654362.29500@g14g2000cwa.googlegroups.com>
References: <epPcMElLGHA.4064@TK2MSFTNGP10.phx.gbl>
NNTP-Posting-Host: 81.171.156.97
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
X-Trace: posting.google.com 1139584965 6762 127.0.0.1 (10 Feb 2006 15:22:45 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Fri, 10 Feb 2006 15:22:45 +0000 (UTC)
In-Reply-To: <epPcMElLGHA.4064@TK2MSFTNGP10.phx.gbl>
User-Agent: G2/0.2
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; InfoPath.1; .NET CLR 2.0.50727),gzip(gfe),gzip(gfe)
Complaints-To: groups-abuse@google.com
Injection-Info: g14g2000cwa.googlegroups.com; posting-host=81.171.156.97;
   posting-account=GH1roQ0AAACgftr1B9S4n3XwqdsCU7E1

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