Google Mail Calendar Documents Reader Web more »
Recently Visited Groups | Help | Sign in
Google Groups Home
microsoft.public.sqlserver.programming
+ new post
About this group
Subscribe to this group
This is a Usenet group - learn more
Message from discussion Guaranteed consecutive identity values
The group you are posting to is a Usenet group. Messages posted to this group will make your email address visible to anyone on the Internet.
Your reply message has not been sent.
Your post was successful
 
From:
To:
Cc:
Follow-up To:
Add Cc | Add Follow-up to | Edit Subject
Subject:
Validation:
For verification purposes please type the characters you see in the picture below or the numbers you hear by clicking the accessibility icon. Listen and type the numbers that you hear
 
David Portas  
View profile   Translate to Translated (View Original)
 More options 10 Feb 2006, 15:22
Newsgroups: microsoft.public.sqlserver.programming
From: "David Portas" <REMOVE_BEFORE_REPLYING_dpor...@acm.org>
Date: 10 Feb 2006 07:22:39 -0800
Local: Fri 10 Feb 2006 15:22
Subject: Re: Guaranteed consecutive identity values
Reply to author | Forward | Print | View thread | Show original | Report this message | Find messages by this author

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


    Reply to author    Forward  
You must Sign in before you can post messages.
To post a message, you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
Create a group - Google Groups - Google Home - Terms of Service - Privacy Policy
©2009 Google