SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Retrieving Identities on set-based inserts


Retrieving Identities on set-based inserts

Author
Message
bobmcc-957585
bobmcc-957585
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 10
Great Article J.
I had no idea about recursive CTE's
Thanks for pointing me in the right direction!
weitzera
weitzera
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1052 Visits: 629
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.'

Florian Reischl
Florian Reischl
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4829 Visits: 3934
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
billhall
billhall
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 48
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.
weitzera
weitzera
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1052 Visits: 629
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.'

SQLRNNR
SQLRNNR
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40863 Visits: 18565
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

Florian Reischl
Florian Reischl
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4829 Visits: 3934
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
Jon Russell
Jon Russell
SSChasing Mays
SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)

Group: General Forum Members
Points: 601 Visits: 1177
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.
Michael H Lutz
Michael H Lutz
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 73
very nice article and follow up discussion!
chrisb 63198
chrisb 63198
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 61
Beautiful and helpful article, thank you:-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search