March 30, 2017 at 8:22 am
Hi,
Is it advisable to use numeric(19,0) as primary key? I do no want to use Bigint because it would require changing corresponding data type in existing application and may cause huge refactoring.
numeric(19,0) would take 9 bytes storage compared to Bigint's 8 bytes but I am fine with it.
If I use numeric(19,0) would it behave like an integer (considering scale is 0) in terms of indexes, joins, etc? Or would there be any overheads because it is a float data type?
Thanks in advance.
Regards,
Kaushik
March 30, 2017 at 9:05 am
When you say huge refactoring of applications what do you mean? Unless an application is directly checking the metadata of the underlying tables it would have no way of knowing whether it got back a 0 precision numeric or an actual integer.
April 2, 2017 at 6:48 pm
NUMERIC(19,0) works just fine for this. It does take 1 extra byte and a small bit of extra time compared to BIGINT wherever and whenever it's used but it works just fine.
That being said, there' personally no way that I'd use NUMERIC(19,0) and any app that can't withstand the change to BIGINT has probably been written incorrectly.
And, that being said, test it on the dev box and see if the app breaks. As a very smart man said, "One good test is worth a thousand expert opinions". 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
April 9, 2017 at 7:35 pm
kaushikchin2 - Thursday, March 30, 2017 8:22 AMOr would there be any overheads because it is a float data type?
Neither DECIMAL nor NUMERIC are "FLOAT" data types.
FLOAT (and REAL) are "approximate" data types, while DECIMAL & NUMERIC are precise.
April 10, 2017 at 3:05 pm
kaushikchin2 - Thursday, March 30, 2017 8:22 AMHi,Is it advisable to use numeric(19,0) as primary key? I do no want to use Bigint because it would require changing corresponding data type in existing application and may cause huge refactoring.
numeric(19,0) would take 9 bytes storage compared to Bigint's 8 bytes but I am fine with it.
If I use numeric(19,0) would it behave like an integer (considering scale is 0) in terms of indexes, joins, etc? Or would there be any overheads because it is a float data type?
Thanks in advance.
Regards,
Kaushik
Please post DDL and follow ANSI/ISO standards when asking for help.
April 10, 2017 at 3:10 pm
kaushikchin2 - Thursday, March 30, 2017 8:22 AMHi,Is it advisable to use numeric(19,0) as primary key? I do no want to use Bigint because it would require changing corresponding data type in existing application and may cause huge refactoring.
numeric(19,0) would take 9 bytes storage compared to Bigint's 8 bytes but I am fine with it.
If I use numeric(19,0) would it behave like an integer (considering scale is 0) in terms of indexes, joins, etc? Or would there be any overheads because it is a float data type?
Thanks in advance.
Regards,
Kaushik
No, of course not. A primary key is in the identifier; and we don't do math on identifiers. Click what's the square root of your credit card number? See how silly it is? People who use numerics like this are really just faking old assembly language pointer chains in RDBMS and haven't learned how to think relationally yet.
Please post DDL and follow ANSI/ISO standards when asking for help.
April 10, 2017 at 7:16 pm
jcelko212 32090 - Monday, April 10, 2017 3:10 PMNo, of course not. A primary key is in the identifier; and we don't do math on identifiers. Click what's the square root of your credit card number? See how silly it is? People who use numerics like this are really just faking old assembly language pointer chains in RDBMS and haven't learned how to think relationally yet.
There you go again with that particular line of misinformation. You still haven't answered the decade old question of what YOU would use for the PK of an Employee table that would survive the long term requirements of being a PK.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 11, 2017 at 11:27 am
First of all, it would be an employee table; it would be "personnel" because would use the name of a set, not a single element unless unless it actually was the set. ISO 11179, metadata committee, and all that standards stuff I keep pulling out .
Jeff, are you really this dumb? There is no such thing as a magic universal generic "employee" in data modeling. Employment is a relationship, which would define how the employee (which might be a person, a lawful person, or placeholder or robot or something else) relates to the employer (which might be a person, lawful person, or something else). The rules for this will depend on what the elements in the model are what the role is in the data model.
The usual business model is to use the tax identification number of the applicable jurisdiction. However there might be some legal restrictions. There is no Kabbalah number for employees; I can use the ISBN for books and publications; I can use the VIN for the vehicles; I can use the GTIN for global trade items; and I also have IASN, and a ton of other indu and stry standards for various categories of very well-defined items that are universally traded. Employees are not that kind of thing
We put it back at you, Jeff; how do you identify a noun (person, place, thing or idea). I think were both old enough when the definition of a noun was used on public televisions Schoolhouse Rock. I will try not to do "conjunction junction" when I talk about logic 🙂
Please post DDL and follow ANSI/ISO standards when asking for help.
April 11, 2017 at 12:47 pm
jcelko212 32090 - Monday, April 10, 2017 3:10 PM
First of all, it would be an employee table; it would be "personnel" because would use the name of a set, not a single element unless unless it actually was the set. ISO 11179, metadata committee, and all that standards stuff I keep pulling out .
Jeff, are you really this dumb? There is no such thing as a magic universal generic "employee" in data modeling. Employment is a relationship, which would define how the employee (which might be a person, a lawful person, or placeholder or robot or something else) relates to the employer (which might be a person, lawful person, or something else). The rules for this will depend on what the elements in the model are what the role is in the data model.
The usual business model is to use the tax identification number of the applicable jurisdiction. However there might be some legal restrictions. There is no Kabbalah number for employees; I can use the ISBN for books and publications; I can use the VIN for the vehicles; I can use the GTIN for global trade items; and I also have IASN, and a ton of other indu and stry standards for various categories of very well-defined items that are universally traded. Employees are not that kind of thing
We put it back at you, Jeff; how do you identify a noun (person, place, thing or idea). I think were both old enough when the definition of a noun was used on public televisions Schoolhouse Rock. I will try not to do "conjunction junction" when I talk about logic 🙂
Heh.... I don't care what you call the bloody table, Joe. Call it what you will. Just stop the interminable rhetoric that helps you avoid posting a solution. Answer the damned question please. If you need a specific example, consider any given national bank. In your terms, what would you use as the PK on a "personnel" table for that given bank that would withstand the test of time and meet the requirements imposed on us for PII. And, no... tax ID won't withstand the test of time for everyone because they can request that it be changed after an identity theft.
And, no... I'm not actually dumb. If I were, I'd fall for the hooie that you continue to regurgitate without actually answering the direct question. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
April 11, 2017 at 2:28 pm
jcelko212 32090 - Monday, April 10, 2017 3:10 PM
First of all, it would be an employee table; it would be "personnel" because would use the name of a set, not a single element unless unless it actually was the set. ISO 11179, metadata committee, and all that standards stuff I keep pulling out .
Jeff, are you really this dumb? There is no such thing as a magic universal generic "employee" in data modeling. Employment is a relationship, which would define how the employee (which might be a person, a lawful person, or placeholder or robot or something else) relates to the employer (which might be a person, lawful person, or something else). The rules for this will depend on what the elements in the model are what the role is in the data model.
The usual business model is to use the tax identification number of the applicable jurisdiction. However there might be some legal restrictions. There is no Kabbalah number for employees; I can use the ISBN for books and publications; I can use the VIN for the vehicles; I can use the GTIN for global trade items; and I also have IASN, and a ton of other indu and stry standards for various categories of very well-defined items that are universally traded. Employees are not that kind of thing
We put it back at you, Jeff; how do you identify a noun (person, place, thing or idea). I think were both old enough when the definition of a noun was used on public televisions Schoolhouse Rock. I will try not to do "conjunction junction" when I talk about logic 🙂
By the way, not every question needs DDL and to follow ANSI/ISO standards.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply