What is the best datatype for holding percent values ranging from 0.00% to 100.00%?
5 Answers
Assuming two decimal places on your percentages, the data type you use depends on how you plan to store your percentages:
- If you are going to store their fractional equivalent (e.g. 100.00% stored as 1.0000), I would store the data in a
decimal(5,4)
data type with aCHECK
constraint that ensures that the values never exceed 1.0000 (assuming that is the cap) and never go below 0 (assuming that is the floor). - If you are going to store their face value (e.g. 100.00% is stored as 100.00), then you should use
decimal(5,2)
with an appropriateCHECK
constraint.
Combined with a good column name, it makes it clear to other developers what the data is and how the data is stored in the column.
Edit: Per modern conventions, we don't name columns indicating the type since the type may change one day and we use contracts and versions to make that indication. Hungarian naming had it's day, and we've moved on to better practices.
-
13Shouldn't it be
decimal(5,2)
where 2 denotes the number of digits after the decimal separator? Commented Jun 15, 2012 at 13:20 -
2@BorisCallens - Can't believe I missed that all these years. Yes, that's a typo.
decimal(5,2)
is what should be captured with a check constraint.– ThomasCommented Jun 15, 2012 at 16:19 -
7I assume this originally had
decimal(5,4)
and was changed todecimal(5,2)
after the above comment... I thinkdecimal(5,4)
would be the better definition - i.e. you want to store 0 to 1 with 2 decimal places, not 0 to 100. The reason being a percentage is out of 100; so 100% is 100/100 which is 1. Doing it this way makes more sense for most cases (e.g.100% * 100% = 100%
, not10000%
;1 * 1 = 1
). Commented May 14, 2014 at 18:45 -
5@JohnLBevan - It spends on how they are being stored. If the values are going to stored as displayed (e.g.
100.00
) then you needdecimal(5,2)
. If the values are going to be stored as fractions (e.g.1.0000
), then you needdecimal(5,4)
. Will update the post.– ThomasCommented May 15, 2014 at 20:59 -
1@MichaelHanon - Depends on the requirement. The OP showed 2 decimal places so I assumed that 99.99% and 100.00% are valid values. If you want percents with 0 decimal places, then you can use
decimal(3,2)
to store them as fractions or a tinyint if you are going to store whole numbers.– ThomasCommented Apr 14, 2018 at 18:52
- Hold as a
decimal
. - Add check constraints if you want to limit the range (e.g. between 0 to 100%; in some cases there may be valid reasons to go beyond 100% or potentially even into the negatives).
- Treat value 1 as 100%, 0.5 as 50%, etc. This will allow any math operations to function as expected (i.e. as opposed to using value 100 as 100%).
- Amend precision and scale as required (these are the two values in brackets
columnName decimal(precision, scale)
. Precision says the total number of digits that can be held in the number, scale says how many of those are after the decimal place, sodecimal(3,2)
is a number which can be represented as#.##
;decimal(5,3)
would be##.###
. decimal
andnumeric
are essentially the same thing. Howeverdecimal
is ANSI compliant, so always use that unless told otherwise (e.g. by your company's coding standards).
Example Scenarios
- For your case (0.00% to 100.00%) you'd want
decimal(5,4)
. - For the most common case (0% to 100%) you'd want
decimal(3,2)
. - In both of the above, the check constraints would be the same
Example:
if object_id('Demo') is null
create table Demo
(
Id bigint not null identity(1,1) constraint pk_Demo primary key
, Name nvarchar(256) not null constraint uk_Demo unique
, SomePercentValue decimal(3,2) constraint chk_Demo_SomePercentValue check (SomePercentValue between 0 and 1)
, SomePrecisionPercentValue decimal(5,2) constraint chk_Demo_SomePrecisionPercentValue check (SomePrecisionPercentValue between 0 and 1)
)
Further Reading:
- Decimal Scale & Precision: http://msdn.microsoft.com/en-us/library/aa258832%28SQL.80%29.aspx
0 to 1
vs0 to 100
: C#: Storing percentages, 50 or 0.50?- Decimal vs Numeric: Is there any difference between DECIMAL and NUMERIC in SQL Server?
-
Hmm, I think your example scenarios are wrong. For
(0.00% to 100.00%)
you needdecimal(5,2)
and for(0% to 100%)
it's better to use int if you don't need decimal points. Commented Dec 1, 2021 at 9:09 -
1Hey @nacholibre; the examples are correct. The important thing to note is that
100%
is held as1.00
; not as100.00
. i.e. 100% of 5 is 5 =>1 x 5 = 5
. 50% of 90 is 45.0.5 x 90 = 45
. Hope that helps to clarify. Commented Dec 1, 2021 at 9:50
I agree with Thomas and I would choose the DECIMAL(5,4) solution at least for WPF applications.
Have a look to the MSDN Numeric Format String to know why : http://msdn.microsoft.com/en-us/library/dwhawy9k#PFormatString
The percent ("P") format specifier multiplies a number by 100 and converts it to a string that represents a percentage.
Then you would be able to use this in your XAML code:
DataFormatString="{}{0:P}"
If 2 decimal places is your level of precision, then a "smallint" would handle this in the smallest space (2-bytes). You store the percent multiplied by 100.
EDIT: The decimal type is probably a better match. Then you don't need to manually scale. It takes 5 bytes per value.
Use numeric(n,n) where n has enough resolution to round to 1.00. For instance:
declare @discount numeric(9,9)
, @quantity int
select @discount = 0.999999999
, @quantity = 10000
select convert(money, @discount * @quantity)
-
3This question has a fairly high rated accepted answer from over three years ago. If you are looking for old questions to answer, please refer here: stackoverflow.com/unanswered– valverijCommented Jul 29, 2013 at 21:14