How to get a batch of identity values without OUTPUT

Thu Jul 16, 2009 by Peter Larsson in algorithms, sql-server-2000

Late this evening I stumbled across a post where OP wanted to get all IDENTITY values for the latest INSERT statement, and correctly OP stated that SCOPE_IDENTITY() only returns last IDENTITY value of the batch.

Well, since OUTPUT is not an option for SQL Server 2000, and if you don't want to include temp tables or other means, this is one way how to solve this issue.
The algorithm relies in implicit transaction; if SPID 60 inserts 5000 records exactly the same time as SPID 61 inserts another 7500 records, the identity values for each spid doesn't get interleaved.

CREATE TABLE #Sample

(

ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,

Dummy INT NOT NULL,

Batch INT NOT NULL

)

INSERT #Sample

SELECT 1, 1

SELECT SCOPE_IDENTITY() - IDENT_INCR('#Sample') * (@@ROWCOUNT - 1) AS FirstID,

SCOPE_IDENTITY() AS LastID,

IDENT_INCR('#Sample') AS Increment

INSERT #Sample

SELECT 2, 2 UNION ALL

SELECT 3, 2 UNION ALL

SELECT 4, 2

SELECT SCOPE_IDENTITY() - IDENT_INCR('#Sample') * (@@ROWCOUNT - 1) AS FirstID,

SCOPE_IDENTITY() AS LastID,

IDENT_INCR('#Sample') AS Increment

INSERT #Sample

SELECT 5, 3 UNION ALL

SELECT 6, 3 UNION ALL

SELECT 7, 3 UNION ALL

SELECT 8, 3 UNION ALL

SELECT 9, 3

SELECT SCOPE_IDENTITY() - IDENT_INCR('#Sample') * (@@ROWCOUNT - 1) AS FirstID,

SCOPE_IDENTITY() AS LastID,

IDENT_INCR('#Sample') AS Increment

SELECT *

FROM #Sample

DROP TABLE #Sample

Legacy Comments


Carl Federl 2009-07-21	re: How to get a batch of identity values without OUTPUT You may be interested in this confirmed bug where SCOPE_IDENTITY() returns an incorrect value when the insert is from a select query that includes paralellism. https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=328811 There was also another situation reported where identity values may get interleaved when the inserted target table is partitioned.

Peso 2009-07-21	re: How to get a batch of identity values without OUTPUT I read the report and it should be fixed in SQL Server 2008. Also I noticed that the SCOPE_IDENTITY() bug exists in SQL Server 2005. Do you know if the bug exists in SQL Server 2000?

Jeff Moden 2009-07-22	re: How to get a batch of identity values without OUTPUT >>The algorithm relies in implicit transaction; if SPID 60 inserts 5000 records exactly the same time as SPID 61 inserts another 7500 records, the identity values for each spid doesn't get interleaved. I'm pretty sure that's not a valid observation, Peter... especially on slower boxes. I'll see if I can find the code that I used to disprove a similar notion before. --Jeff Moden

Joe Celko 2009-07-23	re: How to get a batch of identity values without OUTPUT Yet another reason not to use the count of PHYSICAL insertion attempts to the PHYSICAL disk as ay part of a valid LOGICAL model. The guy even confused rows and records!