microsoft.public.sqlserver.programming

Re: Guaranteed consecutive identity values

Tibor Karaszi <tibor_please.no.email_kara...@hotmail.nomail.com>

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