3

Possible Duplicate:
Best way to get identity of inserted row?

So lets imagine I am building an Auction database for the first time using SQL Server 2012.

Someone creates an auction listing and this is inserted into the database and given a new unique AuctionID. Along with the insertion of the auction, there are many other pieces of data that need to be stored and associated with the auction e.g. images, product properties, etc.

There is a table that holds basic Auction data, and then other tables to store all the other pieces of information that allow for a many-to-many relationship with the Auction.

Now then, in the other tables that hold data about the Auction there would at least be one column that contains the AuctionID and one other column that holds some other info e.g. product specifications.

My way of achieving this (so far) is to first do the insertion of the basic Auction listing and let the database give it a new AuctionID. Fantastic. Next I do this:

SELECT TOP 1 AuctionID FROM Auction
ORDER BY Timestamp DESC

This should return the AuctionID that was last inserted, and if run immediately after the insertion should be the correct one.

I then take this AuctionID, and use that value when inserting the additional information pieces into the other table therefore creating a many-to-many relationship. Viola!

However, is this the correct way to do this when there are millions of transactions happening at the same time so theoretically insertions might be happening within millisecods of each other. Between an insertion taking place, and me retreiving the last inserted AuctionID, I could end up with the wrong AuctionID.

Can anyone please be kind enough to excuse my newbie post and suggest the best way to do this? I have many SQL books in front of me but can't find anything relating to this.

Community
  • 1
  • 1
volume one
  • 6,800
  • 13
  • 67
  • 146
  • 5
    No, don't do that. Definite race condition. Use `SCOPE_IDENTITY()` or the `OUTPUT` clause. See the dupe. – Martin Smith Dec 26 '12 at 22:39
  • 1
    +1 The question is well written and thought out. – asawyer Dec 26 '12 at 22:47
  • Closing this question as duplicate misses an opportunity to provide a new answer based on sequence objects which are now available in SQL Server 2012. The referenced duplicate predates the Denali release – billinkc Dec 26 '12 at 22:53
  • 1
    @billinkc - I see you've added an answer here anyway but the referenced question isn't tagged for any specific version so there's no reason it couldn't be added as an answer there (I've done similar with `FETCH ... OFFSET` and `IIF`) – Martin Smith Dec 26 '12 at 22:55

1 Answers1

2

Assuming this question doesn't get closed, SQL Server 2012 introduces a new, to SQL Server, feature called Sequence numbers. They are similar to defining a column as an identity but they are instead just a global number dispenser---tied to nothing but themselves.

Sequence numbers, can provide better performance versus an identity property due to fewer meta data operations required to use them as well as the ability to set an explicit cache instead of IDENTITY's hard coded 10. Reference

Community
  • 1
  • 1
billinkc
  • 59,250
  • 9
  • 102
  • 159
  • BTW the cache size [looks to have increased since Itzik's article](https://connect.microsoft.com/SQLServer/feedback/details/739013/failover-or-restart-results-in-reseed-of-identity) – Martin Smith Dec 26 '12 at 23:27
  • [And in my tests `IDENTITY` performs **better**](http://dba.stackexchange.com/a/31095/3690) for large batch sizes. – Martin Smith Dec 27 '12 at 15:07