Id of the last inserted
-
I'm just trying to get back to live the DB built on SCOPE_IDENTITY apprach.
You know, it's very good while it's in developer's sandbox.
But when it goes to production, it ends up in 8481 absolutely identiacal rows for a supplier, and 4732 identical rows for the same supplier having "str." instead of "street" in address field. And about 4 thousand rows in total for other variations of the same supplier record.
Developers must be very happy with their design - does not require any thinking.
But now it takes 5 seconds on 4xCPU big RAID server to retrieve an Order by Order Number and Buyer Name.
Good enough?
_____________
Code for TallyGenerator -
IDENTITY is an algorithm and is the "completely sythethic" key that Erland Sommarkskog is referring to in Type C.
I certainly don't mean to pick on anyone but I've seen endless posts on the creation of surrogate keys with all sorts of cool algorityms but I've not seen one, yet, that effectively handles the high-speed, set-based creation of large numbers of keys without some form of counter or IDENTITY WITHOUT using some private, very sensitive info like SSN and still has absolute guarantee that the ID (account number, whatever) will not be duplicated in the future.
Any of you ever see such a thing 'cause I sure haven't...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I see it everyday in all projects I designed.
Probably there is no need to tell you that everything is set based.
Don't know about high-speed. You tell me. 1 second to insert 20-25 columns 60-80 rows flat file into 9 tables references by FKs (including trimming, restoring missing leading zeros and other fixes of impoted data) - is it fast enough?
And because it's financial documentation errors or mismatches are just not allowed.
_____________
Code for TallyGenerator -
Thanks, Serqiy... what does the key look like? And, actually... .1 second to insert only 80 rows across 9 tables seems a bit slow but lemme withhold judgement on that until I see the reasoning behind why a calculated key was necessary...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
By and large we are actually agreeing here Jeff though I disagree that Erland Sommarkskog is referring to an Identity column in type C. That is only there by implication.
I similarly have seen plenty of posts and documentation on this subject and there are plenty of algorithms to generate surrogate keys, If an Identity column fits then there is very little reason to re-invent the wheel but be prepared for the downside.
All 3 of us in this debate have acknowledged that duplicate rows other than the identity value will occur at some stage. One other disadvantage, as far as I am concerned, with ascending sequential keys as the primary key is that new data will always be added at the end of a table and with a high volume, high concurrent system there will be increased contention on tables and/or indexes caused by the choice of the primary key.
Prime place for Identity columns or ascending sequential numbers is in a Data Warehouse. The Primary key of the fact table will by its nature be a large composite key and will be much more efficient with the elements of the composite key as integers. If I remember correctly Kimball has written considerably about this.
The different pro's and con's and the opinons of Chris Date and Joe Celko are well documented. In my view what gets assumed is that if you need a surrogate key it has to be an identity. I have seen many instances where developers have by default added an identity column to every table, subsequent re-design has been known to remove the surrogate key completely.
-
In my view what gets assumed is that if you need a surrogate key it has to be an identity. See the bubble above? That's what I'm talking about... What else would it be other than an IDENTITY? Why would you choose something else? And, if not an IDENTITY, how would you create a high-speed set-based key generation system that could handle large insertions of new data rows if it's not a IDENTITY?
That's all I'm asking, folks. I either agree with or can understand why some folks would agree with most everything else that's been said, so far, including the pros and cons of using natural or surrogate keys... I just want to see a practical example of a surrogate key that is not based on an IDENTITY column (or Oracle sequence, or RowID from various RDBMS's) and doesn't use a GUID datatype and is still guaranteed to be unique and that will beat the IDENTITY column property for performance, reliability, and durability in its generation of large numbers of new surrogate keys! I'm not trying to bust anyone's chops here... I really want to know!
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
The most appropriate method to generate a surrogate key has to depend on a number of factors such as the throughput, concurrency, data volumes and user requirements for access to their data.
There are requirements for a surrogate key that does not have to be high-speed set-based key generation system that could handle large insertions of new data rows. This does not preclude an identity column being used but there may be better options.
While an identity column may well be the most appropriate choice for a surrogate key in the majority of occasions there are times when it isn’t and some consideration of alternates is always worthwhile.
I had a problem a few years back (1994) on an Ingres database with significant contention on row inserts on a table. An ascending sequential key was used and concurrency was such that the system was crippled at peak times. The solution here was to pre-populate look-up tables with a set of IDs. A look-up table was propagated to each client containing 10,000 unique IDs. Therefore each client had a different number range and would target different database pages on inserts and it was only after the local number range was used up that a call was required to the server to get a new range of 10,000 IDs.
This can also be implemented as a lookup table on the server but care is required to ensure that only 1 user gets a specific ID and may introduce further lock contention
There is also the simple select max(cust_id) + 1 from customer
There are many other options out there as I am sure you are aware including the use of an ID number as part of a composite key as Sergiy suggested, the main point here is that the most appropriate method depending on the system design and requirements for generation of surrogate should be applied and not just assumed to be an identity column. There is no single perfect approach and an informed decision should be the way to implement a surrogate key.
I do respect your viewpoint Jeff but reckon will agree to disagree on some aspects of this without being disagreeable.
Bill
-
Sergiy,
Scope_Identity() is an useful function even if not in "developer sand box mode".
If you are having a trigger on the customer table that inserts a row in an audit table, using only @@IDENTITY will return the ID if the audit table, not the customer table.
SCOPE_IDENTITY() manages that problem.
N 56°04'39.16"
E 12°55'05.25" -
Bill,
I'm well aware of NextID tables (sequence tables) and the methods to use them without causing a bottle-neck without any chance of a deadlock or extended wait even during the busiest of times. I'm well aware of many of the different methods to produce calculated surrogate keys including those that use alpha-numeric keys (eg. AA0001, AA0002...). Even those types of keys, which are frequently visible to the consumer/end user, can benefit from the use of an IDENTITY column by using a calculated column and MOD (along with some other mathematical shennanigans) in a high-speed, automatic, bullet-proof manner.
But all of those take extra code (or, God forbid, preloaded key tables for each user, as you suggested) and, when compared to IDENTITY, take a very long time to produce over the course of a million record insert.
My one and only point is that if you must make a surrogate key, it might as well be an IDENTITY in MS-SQL Server (or a Sequence/NextVal in Oracle) because there is nothing faster, there is nothing more reliable, there is nothing less likely to cause a deadlock or a wait, there is zero chance of contention, requires no special handling, and it meets the requirements of a surrogate key. There is not now nor will there ever be any user written SQL code that will produce any surrogate key faster nor more reliably than the built in autonumbering capabilities of IDENTITY... period.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply