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

Message from discussion Guaranteed consecutive identity values
From: "Tibor Karaszi" <tibor_please.no.email_kara...@hotmail.nomail.com>
References: <epPcMElLGHA.4064@TK2MSFTNGP10.phx.gbl>
Subject: Re: Guaranteed consecutive identity values
Date: Fri, 10 Feb 2006 16:21:31 +0100
Lines: 48
MIME-Version: 1.0
Content-Type: text/plain;
	format=flowed;
	charset="iso-8859-1";
	reply-type=response
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
Message-ID: <uNcfpWlLGHA.3896@TK2MSFTNGP15.phx.gbl>
Newsgroups: microsoft.public.sqlserver.programming
NNTP-Posting-Host: h32n2fls31o1111.telia.com 213.64.47.32
Path: g2news1.google.com!news2.google.com!news3.google.com!newshub.sdsu.edu!msrtrans!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP15.phx.gbl

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
>