Getting list of recently added IDENTITY items

[pb648174]

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?

[Erland Sommarskog]

pb648174 (go****@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?

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.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Doug]

sure.
get a timestamp, including milliseconds from the local workstation.
insert that timestamp into a column.
then you can group by timestamp.

[pb648174]

I guess I will just go with a serializable transaction for now...

[David Portas]

pb648174 wrote:

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?

This is easy to solve provided you have an alternate key. IDENTITY
should not be the only key of a table and this is one example of why -
without an alternate key you have no entity integrity so you cannot
always guarantee reliable results from the data.

Try the following example. Notice that the reason this works is that
the INSERT list always must include the primary key when you are
inserting multiple rows (otherwise there is no key). The only potential
exception is where you assign a default value that forms part of the
key - for example DEFAULT CURRENT_TIMESTA MP. In that case you need to
retrieve the default value before you do the INSERT so that you can use
it in the SELECT.

CREATE TABLE tablemania (id INT IDENTITY PRIMARY KEY, a INT, b INT,
UNIQUE (a,b));
CREATE TABLE othertable (a INT, b INT, c INT, PRIMARY KEY (a,b,c));

INSERT INTO othertable (a,b,c)
SELECT 1,2,16 UNION ALL
SELECT 1,3,16 UNION ALL
SELECT 1,4,16 UNION ALL
SELECT 1,5,16 ;

DECLARE @t TABLE (a INT, b INT, PRIMARY KEY (a,b));

INSERT INTO @t (a, b)
SELECT a, b
FROM othertable
WHERE c > 15 ;

INSERT INTO tablemania (a, b)
SELECT a, b
FROM @t ;

SELECT T.id, T.a, T.b
FROM tablemania AS T
JOIN @t AS O
ON T.a = O.a
AND T.b = O.b ;

In SQL Server 2005 you have a more concise alternative. Use the OUTPUT
option:

INSERT INTO tablemania (a, b)
OUTPUT inserted.id, inserted.a, inserted.b
SELECT a, b
FROM othertable
WHERE c > 15 ;

Hope this helps.

--
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/m...S,SQL.90).aspx
--

[David Portas]

Erland Sommarskog wrote:

pb648174 (go****@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/mic...5cbb8f978decc9

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/m...S,SQL.90).aspx
--

[Erland Sommarskog]

David Portas (RE************ *************** *@acm.org) writes:

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/mic...ogramming/msg/
375cbb8f978decc 9

Ah, that was interesting!
This is easy to solve provided you have an alternate key. IDENTITY
should not be the only key of a table and this is one example of why -
without an alternate key you have no entity integrity so you cannot
always guarantee reliable results from the data.
That is about as useful as saying "this is a good car, but you should
not drive it at night". If databases would only include data that
have natural keys, there wouldn't be much data in them.

What I didn't say in my first post, is that my take on this is usually
to not have IDENTITY on my surrogate key, but instead bounce data over
a temp table with IDENTITY, and then add that to a SELECT MAX(id)
from the target table. This comes with scaling problems obviously, but
that has not been an issue for me, luckily.
In SQL Server 2005 you have a more concise alternative. Use the OUTPUT
option:

INSERT INTO tablemania (a, b)
OUTPUT inserted.id, inserted.a, inserted.b
SELECT a, b
FROM othertable
WHERE c > 15 ;

I will have to admit that I not really seen the point with the OUTPUT
clause for INSERT, but this an excellent use for it!

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[David Portas]

Erland Sommarskog wrote:

That is about as useful as saying "this is a good car, but you should
not drive it at night". If databases would only include data that
have natural keys, there wouldn't be much data in them.

You said "data" so you could be right. If you'd said "informatio n"
you'd be wrong. Here's an analogy. If I have two copies of "Inside SQL
Server" on my bookshelf do I have more information than if I have one
copy of that book? Now if I write 1 on the cover of the first book and
2 on the cover of the second, do I have any more information? So should
I spend money and storage space on two books or one? The smart money is
invested in information not in data.

--
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/m...S,SQL.90).aspx
--

[Erland Sommarskog]

David Portas (RE************ *************** *@acm.org) writes:

You said "data" so you could be right. If you'd said "informatio n"
you'd be wrong. Here's an analogy. If I have two copies of "Inside SQL
Server" on my bookshelf do I have more information than if I have one
copy of that book? Now if I write 1 on the cover of the first book and
2 on the cover of the second, do I have any more information? So should
I spend money and storage space on two books or one? The smart money is
invested in information not in data.

Then again, you could have scribbled notes in one of the copies, and
the other could have Kalen's highly valuable autograph.

More importantly, there is data - or information - out there that
users want to - and need to - deal with, despite that we cannot define
a unique key for them. You already know the prime example too well:
customers.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[David Portas]

Erland Sommarskog wrote:

David Portas (RE************ *************** *@acm.org) writes:

You said "data" so you could be right. If you'd said "informatio n"
you'd be wrong. Here's an analogy. If I have two copies of "Inside SQL
Server" on my bookshelf do I have more information than if I have one
copy of that book? Now if I write 1 on the cover of the first book and
2 on the cover of the second, do I have any more information? So should
I spend money and storage space on two books or one? The smart money is
invested in information not in data.
Then again, you could have scribbled notes in one of the copies, and
the other could have Kalen's highly valuable autograph.

In that case they wouldn't be duplicates any more. To complete the
analogy, you now have a natural key. (Alternatively you might want to
decompose the notes and the signature into separate tables)
More importantly, there is data - or information - out there that
users want to - and need to - deal with, despite that we cannot define
a unique key for them. You already know the prime example too well:
customers.

Users are concerned with information. Data (how the information is
represented) is primarily the concern of database professionals. As a
database designer you have a choice because the same information can
always be modelled with natural keys or without. You can argue that the
developer may lack the time, the resources or the authority to redesign
his database. He may even be unable to analyse his business problem
well enough to identify a suitable key with a high degree of
confidence. However, those constraints are not problems we can solve in
a newsgroup. They are project management problems rather than technical
ones.

The technical solution to the OP's problem is simple: elminate
redundancy. It is always possible to eliminate redundancy as a
consequence of the simple fact that duplicate rows cannot contain more
information than a single row. That applies equally whether the row
represents customers or books or anything else. I suggest we let the OP
to decide if he has the will or the resources to implement the solution
in his case.

--
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/m...S,SQL.90).aspx
--