What Should Be the Best Way to Store a Percent Value in SQL-Server

What should be the best way to store a percent value in SQL-Server?

decimal(p, s) and numeric(p, s)

p (precision):

The maximum total number of decimal digits that will be stored (both to the left and to the right of the decimal point)

---

s (scale):

The number of decimal digits that will be stored to the right of the decimal point (-> s defines the number of decimal places)

---

0 <= s <= p.

• p ... total number of digits
• s ... number of digits to the right of the decimal point
• p-s ... number of digits to the left of the decimal point

---

Example:

CREATE TABLE dbo.MyTable
( MyDecimalColumn decimal(5,2)
 ,MyNumericColumn numeric(10,5)
);

INSERT INTO dbo.MyTable VALUES (123, 12345.12);

SELECT MyDecimalColumn, MyNumericColumn FROM dbo.MyTable;

Result:

MyDecimalColumn: 123.00 (p=5, s=2)

MyNumericColumn: 12345.12000 (p=10, s=5)

link: msdn.microsoft.com

Appropriate datatype for holding percent values?

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.

How to store a percentage value?

It depends a little on how you plan to use the value, but typically a decimal would be a good choice for storing percentages. And note that you could store it as the percentage value (10.01) or as the fractional (.1001). So that would affect the actual size and precision of the column.

The choice between storing as a percent or fraction depends on the usage needs, but I suspect that in most situations it would be simpler to store it as a fraction (e.g., .10 to represent 10%) because it can be used directly in most calculations more easily (don't need to remember to divide by 100).

And as @ismaelga points out in the comments, a decimal is a good choice for accuracy (particularly nice when dealing with monetary calculations).

SQL: What do you use to store a ratio (percentage) in a database?

That depends on what your need for accuracy is. If you can tolerate the typical errors that come from the IEEE method of storing floating point numbers, then use a float, otherwise, use a decimal if you need an exact representation (and that goes for any numbers that are not integers that you will use in calculations using the percentage as well).

What would be the best datatype to use to store a simple number up to 99.99

Try DECIMAL(4,2) which would allow you to store -99.99 to 99.99

• The 4 represents the precision which is the total number of digits
  that can be stored (to the left and right of the decimal place).
• The 2 represents the scale which is the number of digits that can
  appear after the decimal place.

Is there a good reason for storing percentages that are less than 1 as numbers greater than 1?

There are actually four good reasons I can think of that you might want to store—and calculate with—whole-number percentage values rather than floating-point equivalents:

1. Depending on the data types chosen, the integer value may take up less space.
2. Depending on the data type, the floating-point value may lose precision (remember that not all languages have a data type equivalent to SQL Server's decimal type).
3. If the value will be input from or output to the user very frequently, it may be more convenient to keep it in a more user-friendly format (decision between convert when you display and convert when you calculate ... but see the next point).

4. If the principle values are also integers, then

   principle * integerPercentage / 100
   

   which uses all integer arithmetic is usually faster than its floating-point equivalent (likely significantly faster in the case of a floating-point type equivalent to T-SQL's decimal type).

Format number as percent in MS SQL Server

M.Ali's answer could be modified as

select Cast(Cast((37.0/38.0)*100 as decimal(18,2)) as varchar(5)) + ' %' as Percentage

How to calculate percentage with a SQL statement

I have tested the following and this does work. The answer by gordyii was close but had the multiplication of 100 in the wrong place and had some missing parenthesis.

Select Grade, (Count(Grade)* 100 / (Select Count(*) From MyTable)) as Score
From MyTable
Group By Grade

What types should I use to represent percentages in C# and SQL Server?

decimal

You won't lose precision due to rounding.

---

Related Topics