May 9, 2020 at 2:09 am
Joe, a SEQUENCE does NOT guarantee contiguity. If you do a rollback because of a bad or failed transaction, the number(s) used in the SEQUENCE will go missing just as surely as they will using the IDENTITY property of a column. And, there's no difference in how the numbering is assigned between SEQUENCE and IDENTITY. They are both based on WHEN you take a number and nothing else.
And both have everything to do with a relational model. Numeric or not, they provide unique identifiers within an entity without regard to what's in the entity, which might not actually be able to be uniquified.
Further, a column with the IDENTITY property is protected from willy-nilly inserts by default where a column that uses SEQUENCES is not.
And all this junk about something being proprietary is just that, junk. Using something proprietary may be a part of the reason why you bought the software with the proprietary feature. The only thing that avoiding proprietary features might support is migration from one engine to another and copy'n'paste migrations are a total myth in the world of SQL so there's absolutely no reason to avoid proprietary features.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 11, 2020 at 1:53 am
I don't understand what a sequence has to do with an identity property. They are two very different things. Just look at all the optional clauses that you get with a create sequence:
[ START WITH <constant> ]
[ INCREMENT BY <constant> ]
[ { MINVALUE [ <constant> ] } | { NO MINVALUE } ]
[ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]
[ CYCLE | { NO CYCLE } ]
This is a lot more than being based on when you'd take a number. One of my favorite tricks is been to set up a cycle so that I know my ticket numbers will be reused to let me mimic physical tickets. The min and max limits are pretty good to, but there's a cute trick with the increment where you have several sequences that increment by different prime numbers. This guarantees that none of their numbers will overlap.
And frankly being able to set insert something directly into a column that was set up with a sequence is an advantage that I don't have an identity column. This comes I can correct any errors, with just simple updates. Of course, it's up to me to set up constraints to check things, but that's true with any table design.
.
The identity table property, on the other hand, doesn't like cycles. It's also not too happy about MIN and MAX.
Please post DDL and follow ANSI/ISO standards when asking for help.
May 11, 2020 at 11:03 am
The SEQUENCE and IDENTITY are two very different things. The IDENTITY table property is a very proprietary, totally non-relational, feature in SQL Server. By analogy, it is like the parking space in the garage in your car is the data element. The parking space number, your assigned space depends on when and where you arrived and has nothing whatsoever to do with a relational model of data.
A SEQUENCE is a particular kind of data structure, with particular mathematical properties. For example, (I am not sure if this is still true) in Italy. It is required by law, that invoice numbers be sequential. If that invoice appears as a data element in more than one place in your schema, then you have to use the same SEQUENCE number. This is one of the advantages of CREATE SEQUENCE (schema level data element value) over IDENTITY (table property having nothing to do with a relational data model).
What other "non-relational" (your error, not mine), proprietary features should license payers not use? Backups are proprietary so no need for that, right? What about log files? Those are proprietary so no need to ship those anywhere, right? Should Oracle users consider not using CURRVAL and NXTVAL because they're proprietary? Ha, no
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
May 11, 2020 at 11:06 am
By analogy, it is like the parking space in the garage in your car is the data element.
So a 2D piece of physical capital is the best analogy you could come up with? No wonder all of your answers and books are useless.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
May 11, 2020 at 1:37 pm
I don't understand what a sequence has to do with an identity property. They are two very different things. Just look at all the optional clauses that you get with a create sequence:
[ START WITH <constant> ]
[ INCREMENT BY <constant> ]
[ { MINVALUE [ <constant> ] } | { NO MINVALUE } ]
[ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]
[ CYCLE | { NO CYCLE } ]
This is a lot more than being based on when you'd take a number. One of my favorite tricks is been to set up a cycle so that I know my ticket numbers will be reused to let me mimic physical tickets. The min and max limits are pretty good to, but there's a cute trick with the increment where you have several sequences that increment by different prime numbers. This guarantees that none of their numbers will overlap.
And frankly being able to set insert something directly into a column that was set up with a sequence is an advantage that I don't have an identity column. This comes I can correct any errors, with just simple updates. Of course, it's up to me to set up constraints to check things, but that's true with any table design.
.
The identity table property, on the other hand, doesn't like cycles. It's also not too happy about MIN and MAX.
In 99.9% of all cases, all most people need is an automatically assigned value incremented by 1. No tricks. No Prime Numbers. No multiple tables (which would be a violation of 3rd normal form and the separation of entities anyway) 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.
You also should not use sequences for anything because you believe that, when used as a PK, they should not be numeric. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
May 11, 2020 at 4:38 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.
Please post DDL and follow ANSI/ISO standards when asking for help.
May 11, 2020 at 5:23 pm
>> Using something proprietary may be a part of the reason why you bought the software with the proprietary feature. <<
I agree about picking your software and hardware for particular proprietary features. As one example it comes to mind, is the use of Teradata and other SQL implementations for insanely large amounts of data. Likewise, SQL engines were designed for very small platforms at the other extreme.
But what I'm buying with my various choices of SQL engines and software is not a totally different SQL; I'm buying implementation of a standard language. Think about all the ways you can read a book that was written in English. You don't need a radically different English for every publication format. In fact, that would completely destroy the usability of English.
>> The only thing that avoiding proprietary features might support is migration from one engine to another and copy'n'paste migrations are a total myth in the world of SQL so there's absolutely no reason to avoid proprietary features. <<
I hate to tell you this, but I've written 10 books and had over 30 years in this trade based on the idea that if you hire me, I can give you code it will easily port from SQL Server, DB2, Oracle, to whatever as long as the next target follows ANSI/ISO standards.
Let me pull out two examples from my SQL Server consulting (yes I do other products). The BIT data type used to be {0,1}, which is how most computer types defined BIT and all the other languages and in general theory. But later, Microsoft changed BIT to mean {0,1, NULL} , as a true numeric data type; in SQL all data types must be able to be NULL. If you never used the BIT data type, you never saw this problem. If you did use the BIT data type, then following the SQL convention of NULL-ability, you would have added a NOT NULL constraint and a default.
My second example is when the infixed outer joins were implemented, replacing the old extended the quality (*=, or +=) notations from Oracle and SQL Server. Oh, the Oracle and the SQL Server notations had different semantics. I coded and commented out the ANSI standard infix notation outer joins on a consulting job. My client gave me a call back when he upgraded his SQL Server and wanted another should rewrite their code. I simply told him to un-comment and test what I had already written for them. This probably cost me a few thousand dollars in consulting fees, but it was the ethical thing to do. It was also the reason you hired me. I know how to avoid proprietary code.
Did you ever read the classic book, "Psychology of Computer Programming" by Jerry Weinberg? His research shows that that software will have the characteristics that the programmer was instructed to obtain. Speed, accuracy, and had a sober anniversary a few years back in a special edition (yes, it's that good!) It's always worth looking at for professionals.
Please post DDL and follow ANSI/ISO standards when asking for help.
May 11, 2020 at 5:37 pm
>> Backups are proprietary so no need for that, right? What about log files? Those are proprietary so no need to ship those anywhere, right? Should Oracle users consider not using CURRVAL and NXTVAL because they're proprietary? Ha, no <<
I hope you understand that backups are not part of the SQL language. Nor are the development environments, archives, security access, and all these other horrible complicated support things we put around our databases. Neither are access methods, which might have to be tuned (B-tree indexes or bit vectors or hashing?). This was the whole thing with the way we define the SQL standards. It's an abstraction, not a description of implementation methods.
My goal with portable code is to be able to take program in this environment, make a few minor changes (the best of course to be no changes whatsoever) and plug it into the new environment that I never have seen before. And it works! You expect this kind of standardization every time you get an electronic device, don't you?
After I get it working (working first, optimize later), then I can tuned it, attach add-ons or whatever. If you can find a good book on it, in the early days of electrification, the generators belong to individual companies that put electrical lights, etc. into neighborhoods. Nobody had agreed on the number of cycles per second wattage voltage or net other stuff. Literally every electrical device was proprietary. You could not plug a Westinghouse motor into an Edison power supply.
Why do you think this is a good way to live?
Please post DDL and follow ANSI/ISO standards when asking for help.
May 12, 2020 at 1:25 pm
After I get it working (working first, optimize later), then I can tuned it, attach add-ons or whatever. If you can find a good book on it, in the early days of electrification, the generators belong to individual companies that put electrical lights, etc. into neighborhoods. Nobody had agreed on the number of cycles per second wattage voltage or net other stuff. Literally every electrical device was proprietary. You could not plug a Westinghouse motor into an Edison power supply.
Why do you think this is a good way to live?
Once again you've got things backwards. SQL Server is a brand new Ferrari. By making maximum use of built-in features it's driven the way it was designed to be driven. On the other hand, in your case, first you build your own crappy steering wheel and install that. Then you take the engine out and install bicycle cranks and pedals because (according to you) that's how all vehicles are supposed to work.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
May 12, 2020 at 6:15 pm
No, it is not a Ferrari. It is a database engine aimed at a particular market niche. It does it very nicely in many ways, but it is not something that can be used for super large databases, nor the really small ones. This is why there are in each markets. If you and had a few decades in IT, you'd realize there is no such thing as "one solution fits all" and we would appreciate for how well a particular product fulfills the needs of its niche..
Before you do automobile analogies, you might want to actually look at the history of the early automobiles. You might want to know your history. Originally, automobiles had not agreed on the idea of a steering wheel or the placement of brakes and accelerators. They didn't agree on whether they should be pedals, throttles or other interfaces. In fact, today military tanks and construction equipment have controls for the left and right tractor treads. This lets these vehicles spin in a circle which your car cannot do with its fixed rear wheels.
How many years (I assume you cannot measure in decades, yet) in IT, do you actually have? You keep trolling this forum with things that indicate either youth or ignorance.
Please post DDL and follow ANSI/ISO standards when asking for help.
May 12, 2020 at 6:53 pm
No, it is not a Ferrari. It is a database engine aimed at a particular market niche. It does it very nicely in many ways, but it is not something that can be used for super large databases, nor the really small ones. This is why there are in each markets. If you and had a few decades in IT, you'd realize there is no such thing as "one solution fits all" and we would appreciate for how well a particular product fulfills the needs of its niche..
Before you do automobile analogies, you might want to actually look at the history of the early automobiles. You might want to know your history. Originally, automobiles had not agreed on the idea of a steering wheel or the placement of brakes and accelerators. They didn't agree on whether they should be pedals, throttles or other interfaces. In fact, today military tanks and construction equipment have controls for the left and right tractor treads. This lets these vehicles spin in a circle which your car cannot do with its fixed rear wheels.
How many years (I assume you cannot measure in decades, yet) in IT, do you actually have? You keep trolling this forum with things that indicate either youth or ignorance.
Until you can crush answers with code I'll keep my opinion that you're a fraud and charlatan
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
May 12, 2020 at 7:43 pm
Let's refrain from any name calling or disparaging remarks and stay professional. You can disagree without implying anyone is less intelligent or capable.
May 12, 2020 at 8:01 pm
>> So a 2D piece of physical capital is the best analogy you could come up with? <<
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?
>> No wonder all of your answers and books are useless.<<
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.
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply