Menu
Considering what MSDN states regarding SQL Server 2008 R2 storage of NUMERIC/DECIMAL precision.
Precision of 1 to 9 is 5 bytes
Precision of 10 to 19 is 9 bytes
So if my business case logically requires a data type with 2 decimal places and a precision of 5 digits it makes no actual performance or storage difference if I define it as NUMERIC(5, 2) or NUMERIC(9, 2).
One considering I'm intentionally ignoring is the implied check constraint as I'd most likely put an actual check constraint on the column limiting the actual allowed range.
Does this make a difference when it comes to indexes, query performance or any other aspect of the system?
346
Precision is the number of digits in a number. Scale is the number of digits to the right of the decimal point in a number. For example, the number 123.45 has a precision of 5 and a scale of 2. In SQL Server, the default maximum precision of numeric and decimal data types is 38.
There is one notable difference between NUMERIC and DECIMAL in standard SQL. The NUMERIC data type is strict; it enforces the exact precision and scale that you have specified. This is in stark contrast to DECIMAL, which allows more numbers than the stated precision.
The DECIMAL and NUMERIC types store exact numeric data values. These types are used when it is important to preserve exact precision, for example with monetary data. In MySQL, NUMERIC is implemented as DECIMAL , so the following remarks about DECIMAL apply equally to NUMERIC .
DECIMAL(18,0) will allow 0 digits after the decimal point. Use something like DECIMAL(18,4) instead that should do just fine! That gives you a total of 18 digits, 4 of which after the decimal point (and 14 before the decimal point).
Numeric(5, 2) allows numbers up to and including 999.99. If you try to insert 1000.0 into that, you'll get an arithmetic overflow.
Numeric(9,2) allows numbers up to and including 9 999 999.99
Bear in mind that if you plan to ever sum this value, allow extra space, otherwise you'll get an overflow or you'll need to do an explicit cast.
They take up the same number of bytes. Since they're the same storage size, they're the same in the data page, in memory, in indexes, in network transmission, etc.
It's for this reason that I usually work out what size number I need to store (if using numeric), then increase precision (and maybe scale) so that I'm just below the point where the storage size increases. So if I need to store up to 99 million with 4 decimal places, that would be numeric (12,4). For the same storage, I can have a numeric (19,6) and give some safe space for when the business user announces that they really do need to store a couple billion in there.
172
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
