Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Retrieving Identities on set-based inserts Expand / Collapse
Author
Message
Posted Wednesday, January 20, 2010 9:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 3, 2010 1:09 PM
Points: 3, Visits: 10
Great Article J.
I had no idea about recursive CTE's
Thanks for pointing me in the right direction!
Post #850618
Posted Wednesday, January 20, 2010 10:06 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:26 PM
Points: 880, Visits: 608
A couple of notes on Florian's post:
The sample script works because you're using Table variables, which are local to the scope. If you use real tables, it is not guaranteed to work, as pointed out in the article, because the IDs of your insert are not guaranteed to be in order or consecutive, so you cannot calculate the IDs of the other rows from the ID of the last row.

Be careful to not confuse an insert of many rows in a single command with a BULK INSERT
http://msdn.microsoft.com/en-us/library/ms188365.aspx




Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
Post #850634
Posted Wednesday, January 20, 2010 10:20 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 1,893, Visits: 3,932
Hi weitzera
weitzera (1/20/2010)
A couple of notes on Florian's post:
The sample script works because you're using Table variables, which are local to the scope. If you use real tables, it is not guaranteed to work, as pointed out in the article, because the IDs of your insert are not guaranteed to be in order or consecutive, so you cannot calculate the IDs of the other rows from the ID of the last row.

Be careful to not confuse an insert of many rows in a single command with a BULK INSERT
http://msdn.microsoft.com/en-us/library/ms188365.aspx


Unfortunately, I don't have any resource which guarantees this works or doesn't work. Just can say I tried this in several combinations with real tables, different orders and up to 50 concurrent processes. It worked in all my tests. (I don't have a complete test environment at the moment.)

Greets
Flo



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #850647
Posted Wednesday, January 20, 2010 11:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 11:06 AM
Points: 2, Visits: 46
Great tip, however, it is important to keep in mind the warning from BOL:

An UPDATE, INSERT, or DELETE statement that has an OUTPUT clause will return rows to the client even if the statement encounters errors and is rolled back. The result should not be used if any error occurs when you run the statement.

Post #850711
Posted Wednesday, January 20, 2010 12:52 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:26 PM
Points: 880, Visits: 608
So I reread what I wrote, and there's actually two issues. The most important one actually has nothing to do with using tables or table variables (you handle this correctly).
Depending on the query plan used, the identity values assigned to rows you insert may or may not be in order, as described in this article:
http://support.microsoft.com/?kbid=273586

You have handled this case by using the maxdop hint and explicitly ordering your rows on each insert.

The second issue is that identity values are assigned outside of the transaction, so within a given transaction the identities are not guaranteed to be sequential if there's ever any data modification against the table. This won't occur if you're using table variables, but can if you're using tables that other queries have access to.

Try running the following queries simultaneously:
Setup
CREATE TABLE foo (fooID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, data1 TIMESTAMP, data2 INT, data3 CHAR(250))
GO
INSERT INTO foo(data2, data3)
SELECT CHECKSUM(name), name
FROM syscolumns
UNION ALL
SELECT CHECKSUM(name+'foo'), name+'foo'
FROM syscolumns

first connection:
SET IMPLICIT_TRANSACTIONS OFF
SET LOCK_TIMEOUT 5
GO
DECLARE @maxID BIGINT, @minID BIGINT
SELECT @maxID = MAX(id), @minID = MIN(ID) FROM sysobjects

INSERT INTO foo (data2, data3)
SELECT TOP(1) CHECKSUM(id), name
FROM sysobjects WHERE id >= (RAND()*(@maxID-@minID))+@minID
GO 20000

Second connection:
DECLARE @sid AS INT;

WITH CTE AS
(
SELECT ROW_NUMBER() OVER (ORDER BY name) AS row,
name
FROM sys.columns
)
INSERT INTO foo (data2, data3)
SELECT row, 'foo123'+name
FROM cte
ORDER BY row DESC
OPTION(MAXDOP 1)

SELECT @sid = SCOPE_IDENTITY()

SELECT fooid, fooid + data2 - @sid, data2
FROM foo
WHERE data3 LIKE 'foo123%'
AND fooid + data2 - @sid <> 1


If you run the second query by itself, it'll return no rows. When I started the first query, and immediately started the second before the first finished, I get identities that no longer can be computed from the scope identity.




Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
Post #850773
Posted Wednesday, January 20, 2010 3:46 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 9:50 AM
Points: 17,854, Visits: 15,802
Thanks for taking the time to write this article.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #850908
Posted Thursday, January 21, 2010 12:58 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 1,893, Visits: 3,932
Hi weitzera

Sorry for the late feedback.

... and thanks for this lesson. Apparently I did all my test wrong. So there seem to be only two ways to get correct identity values from a bulk insert seem to be:

1.)
A uniqueness within the inserted rows and an OUTPUT clause that returns the id and the other unique value. Sadly OUTPUT currently only works for values that are inserted into the destination table.

2.)
The SS2k8 MERGE statement which allows to return values from source table that are not inserted into destination. But MERGE does not give the best performance for many calls with less data.

Thanks again
Flo



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #851526
Posted Thursday, January 21, 2010 1:21 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, October 6, 2014 11:34 AM
Points: 327, Visits: 1,014
Good RBAR article. I have used this before with much success. The code has been in production for a couple of years now. As some have pointed out, just be aware of the pitfalls. As Scott McNitt points out be sure you are aware of any concurency issue.


Post #851545
Posted Thursday, January 21, 2010 1:38 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:26 AM
Points: 21, Visits: 64
very nice article and follow up discussion!
Post #851558
Posted Sunday, November 4, 2012 12:22 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 5, 2013 5:40 AM
Points: 1, Visits: 61
Beautiful and helpful article, thank you
Post #1380815
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse