Menu
I want to store a value that represents a percent in SQL server, what data type should be the prefered one?
884
For the most common case (0% to 100%) you'd want decimal(3,2) .
Finding Percentages between two columns is straightforward. You can simply use the column names and the division operator “/” to divide values in one column by another. The result is a list of values that correspond to the result of the division of all the values in the two columns.
If you need the highest precision, a DECIMAL can use up to 17 bytes for each value. Generally though, I like using DECIMAL(19,4) for currency, which needs 9 bytes and can store numbers 19 digits wide, where the last four digits are after the decimal place.
You should use decimal(p,s) in 99.9% of cases.
Percent is only a presentation concept: 10% is still 0.1.
Simply choose precision and scale for the highest expected values/desired decimal places when expressed as real numbers. You can have p = s for values < 100% and simply decide based on decimal places.
However, if you do need to store 100% or 1, then you'll need p = s+1.
This then allows up to 9.xxxxxx or 9xx.xxxx%, so I'd add a check constraint to keep it maximum of 1 if this is all I need.
101
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
