views:

628

answers:

4

Hello!

I want to store a value that represents a percent in SQL server, what data type should be the prefered one?

+3  A: 

The datatype of the column should be decimal.

OMG Ponies
+4  A: 

I think decimal(p, s) should be used while s represents the percentage capability. the 'p' could of been even 1 since we will never need more than one byte since each digit in left side of the point is one hunderd percent, so the p must be at least s+1, in order you should be able to store up to 1000%. but SQL doesn't allow the 'p' to be smaller than the s.

Examples: 28.2656579879% should be decimal(13, 12) and should be stored 00.282656579879 128.2656579879% should be decimal(13, 12) and should be stored 01.282656579879

28% should be stored in decimal(3,2) as 0.28 128% should be stored in decimal(3,2) as 1.28

Note: if you know that you're not going to reach the 100% (i.e. your value will always be less than 100% than use decimal(s, s), if it will, use decimal(s+1, s).

And so on

Shimmy
and 2134% aka 21.34?
gbn
that's correct.
Shimmy
What are you trying to say? decimal(12, 12) can not store 01.282656579879
gbn
Why ask the question and then give a details answer immediately afterwards...?
Greg Beech
Thanks for the tit-for-tat downvote. However, you really don't understand decimal. Why (s+1, s) for values < 100% ?
gbn
@Greg: to point out what was wrong. Shimmy thought it was correct.
gbn
I think he's conveying that you should set p/s based on your requirements for range (0-99.9, 0-9999, etc.) and precision.
Mayo
@Mayo: but why (s+1, s) for less than 100%?
gbn
@gbn: s+1 is only for when greather than 100%, cuz then you have to store items that reach the first place in the unit (i.e. not smaller than 1).
Shimmy
@Greg, the answer crossed my mind, I thought let's share it.
Shimmy
+7  A: 

decimal(p,s) in 99.9% of cases

Percent is only a presentation concept: 10% is still 0.1

Edit: simply, decide precision and scale for the highest expected values/desired decimal places when expressed as real numbers. You can p = s for values < 100% and simply decide based on decimal places

Edit2:

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.

gbn
I need a value between %100-%0, and it should allow to decimal places (i.e. %xx.xx), I am using decimal(5, 4) and it works best for me.
Shimmy
+2  A: 

I agree, DECIMAL is where you should store this type of number. But to make the decision easier, store it as a percentage of 1, not as a percentage of 100. That way you can store exactly the number of decimal places you need regardless of the "whole" number. So if you want 6 decimal places, use DECIMAL(9, 8) and for 23.3436435%, you store 0.23346435. Changing it to 23.346435% is a display problem, not a storage problem, and most presentation languages / report writers etc. are capable of changing the display for you.

Aaron Bertrand