How to recover identity ID after insert in MSSQL 2008?
-
Only if you don't use functions or nested procedures or triggers. Scope_Identity() is a more robust solution. Learn to use that instead (reteach the fingers).
Never underestimate the power of human stupidity RAH
Yes, but better to get the ID one way or another before inserting the record -- as we did with sequences in Oracle. Identity columns are the devil's work. At work recently I tried to copy some records from one table to another, but couldn't because the IDs wouldn't be the same. X| A thousand deaths upon the idiot who created identity/auto-increment columns. :mad:
-
jschell wrote:
Err...obviously what you quoted is 100% correct.
No you read correctly, this is a fundamental rule of database design and if you ignore it you will get bitten somewhere down the track. A PK has only 1 job, identify the record. Ordering the data is NOT part of the PKs job, thats up to the designer to implement a specific solution. This is one of the strongest arguments FOR guids, it stops developers relying on the PK for anthing but record identification, they can't use them for ordering or to identify part of the the record (the old concatenated string from bits of the record trick). The reson I don't like guids is I can never remember the bloody things when debugging a procedure X| .
Never underestimate the power of human stupidity RAH
Mycroft Holmes wrote:
No you read correctly
Nope. What you quoted had nothing to do with what you then went on to state was wrong.
Mycroft Holmes wrote:
No you read correctly, this is a fundamental rule of database design and if you ignore it you will get bitten somewhere down the track. A PK has only 1 job, identify the record. Ordering the data is NOT part of the PKs job, thats up to the designer to implement a specific solution.
And as I said sometimes ordering is required. As you said as well. From that one then must decide on a way to provide that ordering. And PK integer might be used for that that. Or not. The needs of the application, business, project needs, etc define the design. Not arbitrary rules.
-
jschell wrote:
you are claiming that the need is never possible
True, there is never a need -- and the desire should be stamped out.
jschell wrote:
it does provide a solution
That's the lazy way out. There is likely one or more other fields you should use -- like a record creation timestamp or something, if you want to order records according to the order in which they were created.
PIEBALDconsult wrote:
That's the lazy way out. There is likely one or more other fields you should use -- like a record creation timestamp or something, if you want to order records according to the order in which they were created.
And how would you suggest doing it if you want to uniquely identify records using an a value that allows for a range queries in a database that deals with multiple clients?
-
Yes, but better to get the ID one way or another before inserting the record -- as we did with sequences in Oracle. Identity columns are the devil's work. At work recently I tried to copy some records from one table to another, but couldn't because the IDs wouldn't be the same. X| A thousand deaths upon the idiot who created identity/auto-increment columns. :mad:
PIEBALDconsult wrote:
At work recently I tried to copy some records from one table to another, but couldn't because the IDs wouldn't be the same
I worked with a company that insisted their product worked on Oracle. The schema they delivered obviously originated from MS SQL Server and used quoted identifiers for every single identifier. (My guess - they used a tool to 'convert' the schema prior to delivery to us.) Yet despite that I neither condemn Oracle nor MS SQL Server. I do however blame the company for making claims that obviously they had not designed for. I can only wonder, was the table that you had difficulty with designed to be copied from one location to another while maintaining foreign keys (which I can only presume was the actual problem with ids.) If it wasn't are you suggesting that one should create designs that meet all possible needs of the business of the future? Myself I know that I have yet to meet any developer that is even close to being able to tell the future and the alternative, designing for every possible need, is neither possible nor cost effective. So which of those alternatives are you suggesting business developers should rely on?
-
Mycroft Holmes wrote:
No you read correctly
Nope. What you quoted had nothing to do with what you then went on to state was wrong.
Mycroft Holmes wrote:
No you read correctly, this is a fundamental rule of database design and if you ignore it you will get bitten somewhere down the track. A PK has only 1 job, identify the record. Ordering the data is NOT part of the PKs job, thats up to the designer to implement a specific solution.
And as I said sometimes ordering is required. As you said as well. From that one then must decide on a way to provide that ordering. And PK integer might be used for that that. Or not. The needs of the application, business, project needs, etc define the design. Not arbitrary rules.
I said the PK should NOT be used for anything but the identification of the record.
jschell wrote:
From that one then must decide on a way to provide that ordering. And PK integer might be used for that that.
If ordering is required then add in a specific field to service that requirement, DON'T use the PK field.
jschell wrote:
Not arbitrary rules
There is absolutely nothing arbitrary about these rules. If you don't own the data in the field (it is generated by the database) then you don't control the data. Sybase would randomly skip a few 10k counters, some database system would fill in the gaps, some developers want to fill the gaps.
Never underestimate the power of human stupidity RAH
-
PIEBALDconsult wrote:
At work recently I tried to copy some records from one table to another, but couldn't because the IDs wouldn't be the same
I worked with a company that insisted their product worked on Oracle. The schema they delivered obviously originated from MS SQL Server and used quoted identifiers for every single identifier. (My guess - they used a tool to 'convert' the schema prior to delivery to us.) Yet despite that I neither condemn Oracle nor MS SQL Server. I do however blame the company for making claims that obviously they had not designed for. I can only wonder, was the table that you had difficulty with designed to be copied from one location to another while maintaining foreign keys (which I can only presume was the actual problem with ids.) If it wasn't are you suggesting that one should create designs that meet all possible needs of the business of the future? Myself I know that I have yet to meet any developer that is even close to being able to tell the future and the alternative, designing for every possible need, is neither possible nor cost effective. So which of those alternatives are you suggesting business developers should rely on?
jschell wrote:
was the table that you had difficulty with designed to be copied from one location to another while maintaining foreign keys
Well, I was using the tool built by the infrastructure team for copying data between dev/test/prod, it's the only way to do it... so, yeah, obviously not.
jschell wrote:
any developer that is even close to being able to tell the future
But you should know the past and learn from it.
jschell wrote:
So which of those alternatives are you suggesting business developers should rely on?
Don't use identity/auto-increment columns, because you never know what the future holds.
-
PIEBALDconsult wrote:
That's the lazy way out. There is likely one or more other fields you should use -- like a record creation timestamp or something, if you want to order records according to the order in which they were created.
And how would you suggest doing it if you want to uniquely identify records using an a value that allows for a range queries in a database that deals with multiple clients?
I probably wouldn't; it sounds like a bad idea -- there are times when a developer needs to tell management that an idea is stoooopid. Otherwise, same way -- isn't there a ClientID field as well as a Created field? Or use a custom GUID with such things embedded within it.
-
I probably wouldn't; it sounds like a bad idea -- there are times when a developer needs to tell management that an idea is stoooopid. Otherwise, same way -- isn't there a ClientID field as well as a Created field? Or use a custom GUID with such things embedded within it.
-
I said the PK should NOT be used for anything but the identification of the record.
jschell wrote:
From that one then must decide on a way to provide that ordering. And PK integer might be used for that that.
If ordering is required then add in a specific field to service that requirement, DON'T use the PK field.
jschell wrote:
Not arbitrary rules
There is absolutely nothing arbitrary about these rules. If you don't own the data in the field (it is generated by the database) then you don't control the data. Sybase would randomly skip a few 10k counters, some database system would fill in the gaps, some developers want to fill the gaps.
Never underestimate the power of human stupidity RAH
Mycroft Holmes wrote:
I said the PK should NOT be used for anything but the identification of the record.
You certainly went on to say that. However before doing that you quoted a specific statement and then said it was wrong. And what you quoted was not wrong.
Mycroft Holmes wrote:
If ordering is required then add in a specific field to service that requirement, DON'T use the PK field.
So you are suggesting that one should add a unique sequential value which proceeds in lockstep with the primary key. And one must ignore EVERY other consideration in making that decision, such as complexity, cost, record size, project size and every other possible consideration. Myself I like to base my designs on the business needs and use best practices to make informed decisions as to what what might be best rather than accepting them as commandments from a supreme being.
Mycroft Holmes wrote:
There is absolutely nothing arbitrary about these rules.
It certainly is. If not quote three authorative references that state exactly that.
Mycroft Holmes wrote:
Sybase would randomly skip a few 10k counters, some database system would fill in the gaps, some developers want to fill the gaps.
And I am rather certain that at one time there was no way to generate a GUID as an intrinsic key in Oracle. But the limitations of a specific database or version has nothing at all to do with anything.
-
jschell wrote:
was the table that you had difficulty with designed to be copied from one location to another while maintaining foreign keys
Well, I was using the tool built by the infrastructure team for copying data between dev/test/prod, it's the only way to do it... so, yeah, obviously not.
jschell wrote:
any developer that is even close to being able to tell the future
But you should know the past and learn from it.
jschell wrote:
So which of those alternatives are you suggesting business developers should rely on?
Don't use identity/auto-increment columns, because you never know what the future holds.
PIEBALDconsult wrote:
Well, I was using the tool built by the infrastructure team for copying data between dev/test/prod, it's the only way to do it... so, yeah, obviously not.
So you ran into a poor design. That of course has nothing to do with a specific technological implementation.
PIEBALDconsult wrote:
But you should know the past and learn from it
Correct - when one creates a design that doesn't actually meet the known needs of the business then one should learn to create designs that do meet the needs. Don't see that that has anything to do with this discussion.
PIEBALDconsult wrote:
Don't use identity/auto-increment columns, because you never know what the future holds.
No that isn't the conclusion. The conclusion is that you were dealing with a design that didn't meet the known business needs. Nothing more. There are an infinite number of ways to fail in that regard using absolutely any technology in absolutely any way.
-
PIEBALDconsult wrote:
Or use a custom GUID with such things embedded within it
So your solution is to replicate existing functionality.
No, not really.
-
PIEBALDconsult wrote:
Well, I was using the tool built by the infrastructure team for copying data between dev/test/prod, it's the only way to do it... so, yeah, obviously not.
So you ran into a poor design. That of course has nothing to do with a specific technological implementation.
PIEBALDconsult wrote:
But you should know the past and learn from it
Correct - when one creates a design that doesn't actually meet the known needs of the business then one should learn to create designs that do meet the needs. Don't see that that has anything to do with this discussion.
PIEBALDconsult wrote:
Don't use identity/auto-increment columns, because you never know what the future holds.
No that isn't the conclusion. The conclusion is that you were dealing with a design that didn't meet the known business needs. Nothing more. There are an infinite number of ways to fail in that regard using absolutely any technology in absolutely any way.
Ummmm... so how do you copy records between tables that have identity columns? :confused:
-
Ummmm... so how do you copy records between tables that have identity columns? :confused:
PIEBALDconsult wrote:
Ummmm... so how do you copy records between tables that have identity columns?
If I have a business need to copy records and one which is not just replication and I didn't have any needs that precluded GUIDs then I would use GUIDs. That however is far different than saying that they should be used for every possible scenario.
-
PIEBALDconsult wrote:
Ummmm... so how do you copy records between tables that have identity columns?
If I have a business need to copy records and one which is not just replication and I didn't have any needs that precluded GUIDs then I would use GUIDs. That however is far different than saying that they should be used for every possible scenario.
jschell wrote:
I would use GUIDs.
OK, me too.
jschell wrote:
used for every possible scenario
I'm pretty sure I didn't say they should.
-
jschell wrote:
I would use GUIDs.
OK, me too.
jschell wrote:
used for every possible scenario
I'm pretty sure I didn't say they should.
-
PIEBALDconsult wrote:
I'm pretty sure I didn't say they should.
You said in another subthread "True, there is never a need" in regards to PK as a sequential integer. I took that to mean that one should use GUID - always (implicit within "never").
jschell wrote:
I took that to mean that one should use GUID - always (implicit within "never").
No, that's not what I meant, although many seem to take it that way.
-
Mycroft Holmes wrote:
I said the PK should NOT be used for anything but the identification of the record.
You certainly went on to say that. However before doing that you quoted a specific statement and then said it was wrong. And what you quoted was not wrong.
Mycroft Holmes wrote:
If ordering is required then add in a specific field to service that requirement, DON'T use the PK field.
So you are suggesting that one should add a unique sequential value which proceeds in lockstep with the primary key. And one must ignore EVERY other consideration in making that decision, such as complexity, cost, record size, project size and every other possible consideration. Myself I like to base my designs on the business needs and use best practices to make informed decisions as to what what might be best rather than accepting them as commandments from a supreme being.
Mycroft Holmes wrote:
There is absolutely nothing arbitrary about these rules.
It certainly is. If not quote three authorative references that state exactly that.
Mycroft Holmes wrote:
Sybase would randomly skip a few 10k counters, some database system would fill in the gaps, some developers want to fill the gaps.
And I am rather certain that at one time there was no way to generate a GUID as an intrinsic key in Oracle. But the limitations of a specific database or version has nothing at all to do with anything.
jschell wrote:
you quoted a specific statement and then said it was wrong.
jschell wrote:
One problem with guids is that they have no implicit orderingThis is 100% incorrect
I took it that he disagreed with the "One problem with" part of the statement, as do I. The "guids ... have no implicit ordering" is correct, but it is a benefit, not a problem.