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

Message from discussion Guaranteed consecutive identity values

Tibor Karaszi

View profile

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