May 12, 2020 at 8:33 pm
I I've been taking SQL for many decades, this literally was the best analogy I can come up with. Please give me a better one so I can use it. I have not seen where you have published or taught a better analogy. What property did I miss?
Idk
I am so happy you have read all of my answers over the last 30+ years, at all 10 of my books and determine this! What is likely to be more specific. I really don't think so. I believe you're just one of those little nasty trolls whom I've attracted over the last few decades while I've been the engaged in RDBMS community. I please go away and join the others. When you can make a real contribution, please come back.
blah blah blah....
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
May 12, 2020 at 9:47 pm
>> In 99.9% of all cases, all most people need is an automatically assigned value incremented by 1. <<
No, I would say that in the majority of cases people need a true key, not a locally generated sequential locator for the insertion order on this particular machine. One of my favorite examples of why you don't use identities as a key is to take a set of values and put it in an INSERT INTO statement on one machine, rearrange the data, and then put it into an identical schema on the second machine. If the IDENTITY property could be used as an identifier, then both tables would be exactly the same. But the are not. Another trick (I'm not sure how this one still works) was an insertion statement with a select. You get one run of identities, then change the index or any other access methods on the base table and insert again. The IDENTITY values come out different. We are dealing with a very physical property and not a logical property
>> No multiple tables (which would be a violation of 3rd normal form and the separation of entities anyway) <<
No I think you're missing what I'm trying to say. I get a global SEQUENCE number and assign it to a particular problem ticket. That problem ticket can go to one of several places (refunds, repair shop, etc.). Since the SEQUENCE number is global, it can appear all over my schema taking on different roles. But if I have an IDENTITY property for the refund identifier, there's nothing to stop it from reoccurring as a repair shop ticket number. Local vs global.
I might be unusual in this, but a lot of times I use a SEQUENCE as an integer parameter in a function to generate a computation. I guess I never quite got over the big Sigma notation in my calculus class. 🙂
>> The IDENTITY property of a column works just fine for those cases. And SEQUENCEs don't come with built in by-pass protection for the column. <<
But my SEQUENCE is a standard portable construct. It has well-defined behavior and when I have to move SQL to a different engine, I don't have a lot of problems.
>> You also should not use SEQUENCEs for anything because you believe that, when used as a PK, they should not be numeric. 😉 <<
That's why I encapsulate my "primary key function for this application" as a stored procedure. When I want to use it to get serial numbers of some sort (identifiers), I then CAST() it as a string, pad with leading zeros as needed, add a check digit, and finally add appropriate punctuation, all of this is nicely encapsulated in a function that calls the next element from my SEQUENCE. If the function for creating the serial numbers changes I go to my SEQUENCE and my function and change it one way one place one time in my entire schema.
You and I certainly see things through a different set of eyes.
It doesn't matter if you use a sequence or a column with an Identity property... if you rearrange the data in a table without including either type of column then, just like any new data, the order of the insert will be different.
As for the global ticket thing... if you don't have a centralized table as the source of the global ticket number, you're in violation of normal forms. A column with the Identity property works just fine for such a centralized table.
As for having problems when migrating to other engines, you're always going to have problems because seamless migrations are a myth. Even the concept of Clustered Indexes changes in some RDBMSs and even relatively easy migrations are a myth unless your whole operation was driven only by ORMs and then you have other serious problems. It's better to use the proprietary functionality of whatever RDBMS you're using for best overall functionality and performance whether it be a sequence, Identity column, or whatever with the possible exception of basic single row C.R.U.D.
Heh... and since you're quick to point out that one solution doesn't actually fit all needs (which I agree with), then don't worry about migrations at any point until they happen because, if you do migrate, there's a good chance it's because of some proprietary features that are available in what you're migrating to.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 2 posts - 31 through 31 (of 31 total)
You must be logged in to reply to this topic. Login to reply