Posted by David Portas on 03/11/06 10:30

Erland Sommarskog wrote:
> pb648174 (google@webpaul.net) writes:
> > In a multi-user environment, I would like to get a list of Ids
> > generated, similar to:
> >
> > declare @LastId int
> > select @LastId = Max(Id) From TableMania
> >
> > INSERT INTO TableMania (ColumnA, ColumnB)
> > SELECT ColumnA, ColumnB From OtherTable Where ColumnC > 15
> >
> > --get entries just added
> > SELECT * FROM TableMania WHERE Id > @LastId
> >
> >
> > The above works fine, except I'm assuming it will not work in a
> > multi-user environment. Is there any way to get the set of Ids that
> > were just added in the previous statement (similar to @@IDENTITY)
> > without doing all of this in a serializable transaction or making a
> > temp table of every single Id before the insert statement?
>
> Actually, I don't know.
>
> Say that you insert a couple of rows into a table with a column ident
> that has the IDENTITY property. @@identity or scope_identity gives
> you the highest value for ident for the lnserted rows. But is the
> lowest value @@identity - @@rowcount + 1?
>
> I have never seen any documentation that guarantees this to be true.
> It is likely to be, but what if you insert 10000 rows, and in the
> middle of this another users needs to insert a single row. Will he
> steal a value?
>

Tibor posted a repro that demonstrates the values are not always
contiguous. Also there is a related problem with IGNORE_DUP_KEY, which
causes gaps if rows are ignored.
http://groups.google.co.uk/group/microsoft.public.sqlserver.programming/msg/375cbb8f978decc9


> One strategy would be to retrieve ident_current() before the insertion (or
> MAX(ident), and then after the insertion check that the interval is equal
> to @@rowcount, and bail out if it's not.
>

See my solutions in this thread.

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

[Back to original message]