About Me

My photo
Northglenn, Colorado, United States
I'm primarily a BI Developer on the Microsoft stack. I do sometimes touch upon other Microsoft stacks ( web development, application development, and sql server development).

Monday, February 06, 2012

Multi-Processor - Parallel SQL does not work well with SCOPE_IDENTITY()

Just something to be aware of, that was brought up at work. When working with parallel processing, the SCOPE_IDENTITY() might bring back the wrong id. This is true for SQL Server 2008 R2 and lower. This is suppose to be fix for SQL Server 2012.





Mean while, use the OUTPUT to get the newly inserted value, if need be.


OLD*******************************

 INSERT INTO [Database1].[dbo].[Table1]
([Field1]
,[LDTS]
,[RS])
VALUES(@Field1, @LDTS, @RS)

SET @FieldID = SCOPE_IDENTITY()


NEW******************************

Declare
@GetID Table (FieldID smallint)

INSERT INTO [Database1].[dbo].[Table1]
([Field1]
,[LDTS]
,[RS])
OUTPUT Inserted.FieldID into @GetID      
VALUES(@Field1, @LDTS, @RS)

Select Top 1  @FieldID = FieldID from @GetID

No comments: