Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

How to get a batch of identity values without OUTPUT

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

Print | posted on Thursday, July 16, 2009 11:37 PM | Filed Under [ Algorithms SQL Server 2000 ]

Feedback

Gravatar

# 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.
7/21/2009 11:01 AM | Carl Federl
Gravatar

# 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?
7/21/2009 3:40 PM | Peso
Gravatar

# 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
7/22/2009 4:10 AM | Jeff Moden
Gravatar

# 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!
7/23/2009 2:02 AM | Joe Celko
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET