November 1, 2016 at 9:15 am
We have ID columns with values with up to 10 digits. Those ID's are usually third (and last) segment in indices on various tables. Normally I define them as NUMERIC(10), but looking into other people's code, I see they have lately (from SQL 2014) started to define them as DECIMAL(10,0). I would assume it is a bad idea, especially when it comes to JOIN expressions involving tableA.ID = tableB.ID, but I do not have enough knowledge to address it nor could find something, when searching for SQL2014
November 1, 2016 at 9:37 am
Decimal and numeric are synonyms in SQL Server. It doesn't matter if you choose one or the other, although it's a good practice to be consistent.
On the other side, why aren't you using int or even bigint for really large tables?
November 1, 2016 at 9:48 am
Luis Cazares (11/1/2016)
Decimal and numeric are synonyms in SQL Server. It doesn't matter if you choose one or the other, although it's a good practice to be consistent.On the other side, why aren't you using int or even bigint for really large tables?
Thank you, Luis, for the reply.
INT in this case won't work, as any ID of 10 digits over 2147483647 will fail, I presume. With regards to BIGINT, I admit I have never used it before, hence do not know
what affect it might have on our DB (number of records varies between 1 and 9 million from customer to customer) .
November 1, 2016 at 10:09 am
If you have 9 million rows, int can handle them perfectly. Unless you have a random sequence or something like this that might need to have always 10 digits (even then, there would be over a billion (10^9) values). All this at the storage cost of 4 Bytes per row.
Bigint will double the bytes ( 8), but will give you mover values than you'll ever need.
Right now, using decimal(10) requires 9 Bytes.
Someone would say that if you want to handle like a 10 digit value every time, you could go with a char(10) with a check constraint. The only issue is that it will add one byte to the storage used by decimal, but will also allow you to have leading zeros, which are not available on numeric values but could be added for display.
One million bytes are under a MB which is unexpensive this days, but adding the small improvements, you might get a great result.
November 1, 2016 at 10:35 am
Luis Cazares (11/1/2016)
If you have 9 million rows, int can handle them perfectly. Unless you have a random sequence
Sadly you're right - every new customer starts from 2 to 3 reserved digits, e.g. 3500000001, hence INT will fail. I will stick to NUMERIC at the moment, to be compatible with other tables, but wanted to know if there is a difference between NUMERIC(10) and DECIMAL (10,0) people have started to use.
November 1, 2016 at 10:57 am
BOR15K (11/1/2016)
Luis Cazares (11/1/2016)
If you have 9 million rows, int can handle them perfectly. Unless you have a random sequenceSadly you're right - every new customer starts from 2 to 3 reserved digits, e.g. 3500000001, hence INT will fail. I will stick to NUMERIC at the moment, to be compatible with other tables, but wanted to know if there is a difference between NUMERIC(10) and DECIMAL (10,0) people have started to use.
NUMERIC and DECIMAL are equivalent (link). Adding (10) or (10,0) to either is also equivalent, because the default precision for DECIMAL/NUMERIC is zero.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 1, 2016 at 10:58 am
BOR15K (11/1/2016)
Luis Cazares (11/1/2016)
If you have 9 million rows, int can handle them perfectly. Unless you have a random sequenceSadly you're right - every new customer starts from 2 to 3 reserved digits, e.g. 3500000001, hence INT will fail. I will stick to NUMERIC at the moment, to be compatible with other tables, but wanted to know if there is a difference between NUMERIC(10) and DECIMAL (10,0) people have started to use.
Wrong decision I think. Decimal and Numeric both are NINE bytes of storage per row while a bigint is just EIGHT bytes. Plus CPUs have sections that specialize in integer processing.
https://msdn.microsoft.com/en-us/library/ms187746.aspx
If you don't think that one byte matters that is another discussion. 😀
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 1, 2016 at 11:06 am
TheSQLGuru (11/1/2016)
BOR15K (11/1/2016)
Luis Cazares (11/1/2016)
If you have 9 million rows, int can handle them perfectly. Unless you have a random sequenceSadly you're right - every new customer starts from 2 to 3 reserved digits, e.g. 3500000001, hence INT will fail. I will stick to NUMERIC at the moment, to be compatible with other tables, but wanted to know if there is a difference between NUMERIC(10) and DECIMAL (10,0) people have started to use.
Wrong decision I think. Decimal and Numeric both are NINE bytes of storage per row while a bigint is just EIGHT bytes. Plus CPUs have sections that specialize in integer processing.
https://msdn.microsoft.com/en-us/library/ms187746.aspx
If you don't think that one byte matters that is another discussion. 😀
Correct, unless you want to use foreign key. Having a new table with BIGINT and original one - NUMERIC(10) will not allow you to create any foreign key.
I am not in a position to start changing all live DB's from NUMERIC(10) to BIGINT.
November 1, 2016 at 7:06 pm
BOR15K (11/1/2016)
TheSQLGuru (11/1/2016)
BOR15K (11/1/2016)
Luis Cazares (11/1/2016)
If you have 9 million rows, int can handle them perfectly. Unless you have a random sequenceSadly you're right - every new customer starts from 2 to 3 reserved digits, e.g. 3500000001, hence INT will fail. I will stick to NUMERIC at the moment, to be compatible with other tables, but wanted to know if there is a difference between NUMERIC(10) and DECIMAL (10,0) people have started to use.
Wrong decision I think. Decimal and Numeric both are NINE bytes of storage per row while a bigint is just EIGHT bytes. Plus CPUs have sections that specialize in integer processing.
https://msdn.microsoft.com/en-us/library/ms187746.aspx
If you don't think that one byte matters that is another discussion. 😀
Correct, unless you want to use foreign key. Having a new table with BIGINT and original one - NUMERIC(10) will not allow you to create any foreign key.
I am not in a position to start changing all live DB's from NUMERIC(10) to BIGINT.
Hopefully you can educate the architect(s) so this doesn't keep happening in the future. Smallest data type for the data is a valuable mantra.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply