Reputation: 66111
What is the best datatype for holding percent values ranging from 0.00% to 100.00%?
Upvotes: 160
Views: 262326
Reputation: 64674
Assuming two decimal places on your percentages, the data type you use depends on how you plan to store your percentages:
decimal(5,4)
data type with a CHECK
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).decimal(5,2)
with an appropriate CHECK
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.
Upvotes: 185
Reputation: 850
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}"
Upvotes: 4
Reputation: 24490
decimal
.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, so decimal(3,2)
is a number which can be represented as #.##
; decimal(5,3)
would be ##.###
. decimal
and numeric
are essentially the same thing. However decimal
is ANSI compliant, so always use that unless told otherwise (e.g. by your company's coding standards).Example Scenarios
decimal(5,4)
.decimal(3,2)
.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:
0 to 1
vs 0 to 100
: C#: Storing percentages, 50 or 0.50?Upvotes: 42
Reputation: 1
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)
Upvotes: -1
Reputation: 57787
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.
Upvotes: 2