Google Groups Home
Help | Sign in
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
 
Tibor Karaszi  
View profile   Translate to Translated (View Original)
 More options 10 Feb 2006, 15:21
Newsgroups: microsoft.public.sqlserver.programming
From: "Tibor Karaszi" <tibor_please.no.email_kara...@hotmail.nomail.com>
Date: Fri, 10 Feb 2006 16:21:31 +0100
Local: Fri 10 Feb 2006 15:21
Subject: Re: Guaranteed consecutive identity values
Reply to author | Forward | Print | View thread | Show original | Report this message | Find messages by this author
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.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/

"Michael Abraham" <mabraha...@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


    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