Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Appropriate datatype for holding percent values?

What is the best datatype for holding percent values ranging from 0.00% to 100.00%?

like image 540
User Avatar asked May 04 '10 01:05

User


People also ask

What data type should be used for percentage?

decimal(p,s) / float(n) / real – Are used to store decimal numbers. We can expect that most numerical values we want to store are actually decimal values – percentage, graphical coordinates, sports results etc. bit – Uses only 1 bit to store value 0 or 1 (NULL if not defined).

How do you store percentages in a database?

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.

What is the data type for percentage in SQL?

Finding Percentage using Two Variables You have to rely on basic arithmetic operations i.e. (number1/number2 x 100) to find percentages in SQL Server.

What datatype is used for percentage in C#?

The percent ("P") format specifier is used to multiply a number by 100. It converts the number into a string representing a % (percentage). In the same way, using (“P1”), would only include a single vale after decimal-point.


2 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 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).
  • 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 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.

like image 103
Thomas Avatar answered Oct 03 '22 08:10

Thomas


  • 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, 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

  • 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 vs 0 to 100: C#: Storing percentages, 50 or 0.50?
  • Decimal vs Numeric: Is there any difference between DECIMAL and NUMERIC in SQL Server?
like image 22
JohnLBevan Avatar answered Oct 03 '22 09:10

JohnLBevan