How to store decimal values in SQL Server?
Solution 1:
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).
Solution 2:
You should use is as follows:
DECIMAL(m,a)
m is the number of total digits your decimal can have.
a is the max number of digits you can have after the decimal point.
http://www.tsqltutorials.com/datatypes.php has descriptions for all the datatypes.
Solution 3:
The settings for Decimal are its precision and scale or in normal language, how many digits can a number have and how many digits do you want to have to the right of the decimal point.
So if you put PI into a Decimal(18,0) it will be recorded as 3?
If you put PI into a Decimal(18,2) it will be recorded as 3.14?
If you put PI into Decimal(18,10) be recorded as 3.1415926535.
Solution 4:
For most of the time, I use decimal(9,2) which takes the least storage (5 bytes) in sql decimal type.
Precision => Storage bytes
- 1 - 9 => 5
- 10-19 => 9
- 20-28 => 13
- 29-38 => 17
It can store from 0 up to 9 999 999.99 (7 digit infront + 2 digit behind decimal point = total 9 digit), which is big enough for most of the values.
Solution 5:
You can try this
decimal(18,1)
The length of numbers should be totally 18. The length of numbers after the decimal point should be 1 only and not more than that.