What should be the best way to save a percentage in SQL Server?

Question

I want to save a value representing the percentage on the SQL server, which data type should be preferred?

Answer 1

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

p (accuracy):

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

---

s (scale):

The number of decimal digits to be stored to the right of the decimal point (-> s, determines the number of decimal places)

---

0 <= s <= p.

• p ... total number of digits
• s ... the number of digits to the right of the decimal point
• ps ... the 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

Answer 2

You should use decimal (p, s) in 99.9% of cases.

Interest is just a presentation concept: 10% is still 0.1.

Simply select the precision and scale for the highest expected values ​​/ desired decimal places, if expressed as real numbers. You can have p = s for values ​​<100% and just decide based on decimal places.

However, if you need to save 100% or 1, you will need p = s + 1.

This allows up to 9.xxxxxx or 9xx.xxxx%, so I would add a control limit so that it does not exceed 1 if that’s all I need.

Answer 3

I think decimal (p, s) should be used, and s represents percentage ability. "p" can be equal to 1, since we do not need more than one byte, since each digit on the left side of the point is equal to one percent, so the value of p must be at least s + 1 so that you can store up to 1000%. but SQL does not allow 'p' to be smaller than s.

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

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 will not reach 100% (i.e. your value will always be less than 100% than using the decimal number (s, s), if it is, use the decimal (s + 1, s).

And so on

Answer 4

I agree, DECIMAL is the place where you should store this type of number. But to make the solution easier, save it as a percentage of 1, not a percentage of 100. Thus, you can accurately store the number of decimal places you need, regardless of the "integer" number. Therefore, if you want 6 decimal places, use DECIMAL (9, 8) and 23.3436435%, you store 0.23346435. Changing it to 23.346435% is a display problem, not a storage problem, but most presentation languages ​​/ report authors, etc. Able to change the display for you.

Answer 5

The column data type must be decimal.