SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Steve McRoberts / Retrieving Identities on set-based inserts / Latest Posts

RE: Retrieving Identities on set-based inserts

chrisb 63198 — Sun, 04 Nov 2012 12:22:39 GMT

Beautiful and helpful article, thank you:-)

RE: Retrieving Identities on set-based inserts

Michael H Lutz — Thu, 21 Jan 2010 13:38:07 GMT

very nice article and follow up discussion!

RE: Retrieving Identities on set-based inserts

Jon Russell — Thu, 21 Jan 2010 13:21:48 GMT

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.

RE: Retrieving Identities on set-based inserts

Florian Reischl — Thu, 21 Jan 2010 12:58:49 GMT

Hi weitzeraSorry for the late feedback.... and thanks for this [i]lesson[/i]. 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 againFlo

RE: Retrieving Identities on set-based inserts

SQLRNNR — Wed, 20 Jan 2010 15:46:50 GMT

Thanks for taking the time to write this article.

RE: Retrieving Identities on set-based inserts

weitzera — Wed, 20 Jan 2010 12:52:39 GMT

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=273586You 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[code="sql"]CREATE TABLE foo (fooID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, data1 TIMESTAMP, data2 INT, data3 CHAR(250))GOINSERT INTO foo(data2, data3)SELECT CHECKSUM(name), nameFROM syscolumnsUNION ALLSELECT CHECKSUM(name+'foo'), name+'foo'FROM syscolumns[/code]first connection:[code="sql"]SET IMPLICIT_TRANSACTIONS OFFSET LOCK_TIMEOUT 5GODECLARE @maxID BIGINT, @minID BIGINTSELECT @maxID = MAX(id), @minID = MIN(ID) FROM sysobjectsINSERT INTO foo (data2, data3)SELECT TOP(1) CHECKSUM(id), nameFROM sysobjects WHERE id >= (RAND()*(@maxID-@minID))+@minIDGO 20000[/code]Second connection:[code="sql"]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'+nameFROM cteORDER BY row DESCOPTION(MAXDOP 1)SELECT @sid = SCOPE_IDENTITY()SELECT fooid, fooid + data2 - @sid, data2FROM fooWHERE data3 LIKE 'foo123%' AND fooid + data2 - @sid <> 1[/code]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.

RE: Retrieving Identities on set-based inserts

billhall — Wed, 20 Jan 2010 11:28:37 GMT

Great tip, however, it is important to keep in mind the warning from BOL:[i]An UPDATE, INSERT, or DELETE statement that has an OUTPUT clause will return rows to the client [b]even if the statement encounters errors and is rolled back[/b]. The result should not be used if any error occurs when you run the statement.[/i]

RE: Retrieving Identities on set-based inserts

Florian Reischl — Wed, 20 Jan 2010 10:20:45 GMT

Hi weitzera[quote][b]weitzera (1/20/2010)[/b][hr]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 INSERThttp://msdn.microsoft.com/en-us/library/ms188365.aspx[/quote]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.)GreetsFlo

RE: Retrieving Identities on set-based inserts

weitzera — Wed, 20 Jan 2010 10:06:11 GMT

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 INSERThttp://msdn.microsoft.com/en-us/library/ms188365.aspx

RE: Retrieving Identities on set-based inserts

bobmcc-957585 — Wed, 20 Jan 2010 09:50:28 GMT

Great Article J.I had no idea about recursive CTE'sThanks for pointing me in the right direction!

RE: Retrieving Identities on set-based inserts

bobmcc-957585 — Wed, 20 Jan 2010 09:17:23 GMT

Thanks J!I'm on my way to check out the article you recommended. I appreciate the reply...bobHi BobThis is a recursive CTE, which is a supported structure in TSQLOne nice use for it is to navigate a parent-child relationship of uncertain depth. MSDN has an article on it at http://msdn.microsoft.com/en-us/library/ms186243.aspx

RE: Retrieving Identities on set-based inserts

jdurandt — Wed, 20 Jan 2010 09:03:30 GMT

[quote][b]bobmcc-957585 (1/20/2010)[/b][hr]This is a sidebar but....I am struggling with how the CTE is referencing itself from within itselfThis works but I need clarification on it. The CTE is referencing itself from within itself.I did not realize this was possible. Any clarification would be greatly appreciated.;WITH mycte AS (SELECT 100 AS seedValueUNION ALL SELECT seedValue + 1 FROM mycte WHERE seedValue + 1 < 200)select * from mycte[/quote]Hi BobThis is a recursive CTE, which is a supported structure in TSQLOne nice use for it is to navigate a parent-child relationship of uncertain depth. MSDN has an article on it at http://msdn.microsoft.com/en-us/library/ms186243.aspx

RE: Retrieving Identities on set-based inserts

bobmcc-957585 — Wed, 20 Jan 2010 08:46:10 GMT

This is a sidebar but....I am struggling with how the CTE is referencing itself from within itselfThis works but I need clarification on it. The CTE is referencing itself from within itself.I did not realize this was possible. Any clarification would be greatly appreciated.;WITH mycte AS (SELECT 100 AS seedValueUNION ALL SELECT seedValue + 1 FROM mycte WHERE seedValue + 1 < 200)select * from mycte

RE: Retrieving Identities on set-based inserts

scott mcnitt — Wed, 20 Jan 2010 07:44:40 GMT

I like the idea of using the OUTPUT clause but I have this bad feeling about the example. Perhaps there are requirements that justified doing things this way (insert all parent rows then insert all children), but I think this will cause problems with concurrency.It seems we are always "fighting" against such requirements as "add all records to the database; rollback all changes for the batch if any errors". When the batch gets large, the locking may escalate to table locks. How large is a dynamic that is based on resources at the time.I guess my point is to be aware that, if you can, try to make the unit of work one master and all its related child records.

RE: Retrieving Identities on set-based inserts

Florian Reischl — Wed, 20 Jan 2010 04:45:00 GMT

Hi %[quote][b]kll (1/20/2010)[/b][hr]Just remember that using any kind of @@identity or scope_identity() will still require some RBAR on multi line inserts.[/quote]It's possible to use bulk insert operations if table containing the new rows to be inserted contains any kind of a surrogate id (or another uniqueness).[code]---========================================================-- table definitions------------------------------------------------------------- destination table for new data to be insertedDECLARE @Destination TABLE ( Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED ,SomeInt INT);------------------------------------------------------------- table containing all rows to be newly inserted into-- destination table-- IMPORTANT-- the table containing the new data to be inserted requires-- any kind of uniqueness (e.g. a surrogate id)DECLARE @NewData TABLE ( SurrogateId INT NOT NULL PRIMARY KEY CLUSTERED ,SomeInt INT);---========================================================-- create some sample data------------------------------------------------------------- insert existing data into destination tableINSERT INTO @Destination SELECT -777 UNION ALL SELECT -888;------------------------------------------------------------- new rows to be inserted into destination tableINSERT INTO @NewData SELECT -1, 11 UNION ALL SELECT -2, 22;---========================================================-- bulk insert rows into destination table and catch new identities------------------------------------------------------------- variables to catch count of inserted rows and scope-identityDECLARE @LastIdentity INT ,@RowCount INT;------------------------------------------------------------- bulk insert new rows into destination table-- IMPORTANT-- to get the following operations work ensure to use an-- ORDER BY in SELECT. To avoid problems due to paralellism-- use MAXDOP 1 option what should be okay for an INSERTINSERT INTO @Destination SELECT SomeInt FROM @NewData ORDER BY SurrogateId OPTION (MAXDOP 1);------------------------------------------------------------- catch the last created scope-identity and the count of-- rows insertedSELECT @LastIdentity = SCOPE_IDENTITY() ,@RowCount = COUNT(*) FROM @NewData;------------------------------------------------------------- use a CTE that creates a row number ordered by the -- surrogate id of newly inserted rowsWITH cte AS ( SELECT SurrogateId ,ROW_NUMBER() OVER (ORDER BY SurrogateId) RowNum FROM @NewData)SELECT * -- calculate destination table IDENTITY values ,@LastIdentity - @RowCount + RowNum FROM cte[/code]GreetsFlo

RE: Retrieving Identities on set-based inserts

Kevin O'Donovan — Wed, 20 Jan 2010 03:47:46 GMT

[quote][b]kll (1/20/2010)[/b][hr]Good to see examples of the output clause.The @@identity problem should be a no-problem, if people just remembered to use scope_identity() instead. It will return the correct identity value to you. No identities from triggers..[/quote]If you're using the output clause then you don't need to use either of those. I'd still use scope_identity for single row inserts though[quote]Just remember that using any kind of @@identity or scope_identity() will still require some RBAR on multi line inserts.[/quote]Surely the whole point of this article is that you [b]don't[/b] need to do any RBAR. I use this technique a lot. Best result was on a stored proc that inserted typically about 500 top level records, each consisting of several sub-hierarchies ranging from one to 4 levels deep. Execution time went down from about 20 minutes to a few seconds.

RE: Retrieving Identities on set-based inserts

Kevin O'Donovan — Wed, 20 Jan 2010 03:32:45 GMT

[quote][b]Paul Marriott-463518 (1/19/2010)[/b][hr]I can see how this will return the set of inserted values. However, I am not sure that I understand just how we can match this set of inserted identity values to the rows we have just inserted. To elaborate - lets assume we are inserting two PERSON records. We have separate addresses for each PERSON. Both of the individuals are called "John Smith", with the same birth date. So how can we be sure that we have successfully matched address to the right JOHN SMITH? Or must we be able to uniquely identify the inserted rows to be able to successfully match them to the identities associated with the inserted row?[/quote]There are a few options:1) put enough columns in the output clause to uniquely identify the inserted rows. Can get a bit messy though, and if there's the possibliity two rows can be genuinely identical then you could end up with some very strange results2) You can use order by on the select statement used to insert your top level data, which will guarantee your IDs match the row ordering you've chosen. Whether this is useful depends on the rest of your data3) Add a rowguid column to the tables where you ened to do this, and add that to your output clause. That'll always do the trickKev

RE: Retrieving Identities on set-based inserts

kll — Wed, 20 Jan 2010 00:49:54 GMT

Good to see examples of the output clause.The @@identity problem should be a no-problem, if people just remembered to use scope_identity() instead. It will return the correct identity value to you. No identities from triggers.Just remember that using any kind of @@identity or scope_identity() will still require some RBAR on multi line inserts.

RE: Retrieving Identities on set-based inserts

Paul Marriott-463518 — Tue, 19 Jan 2010 21:37:23 GMT

I can see how this will return the set of inserted values. However, I am not sure that I understand just how we can match this set of inserted identity values to the rows we have just inserted. To elaborate - lets assume we are inserting two PERSON records. We have separate addresses for each PERSON. Both of the individuals are called "John Smith", with the same birth date. So how can we be sure that we have successfully matched address to the right JOHN SMITH? Or must we be able to uniquely identify the inserted rows to be able to successfully match them to the identities associated with the inserted row?

Retrieving Identities on set-based inserts

Steve McRoberts-357330 — Tue, 19 Jan 2010 20:50:34 GMT

Comments posted to this topic are about the item [B]Retrieving Identities on set-based inserts[/B]