CODEWITHSUNDEEP
sqlsql-servert-sqltypes
Shimmy Weitzhandler
Shimmy Weitzhandler

Reputation: 104841

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

I want to store a value that represents a percent in SQL server, what data type should be the prefered one?

Upvotes: 92

Views: 128715

Answers (5)

Guti_Haz
Guti_Haz

Reputation: 2728

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

Upvotes: 66

gbn
gbn

Reputation: 432742

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.

However, if you do need to store 100% or 1, then you'll need p = s+1.

This then allows up to 9.xxxxxx or 9xx.xxxx%, so I'd add a check constraint to keep it maximum of 1 if this is all I need.

Upvotes: 85

Aaron Bertrand
Aaron Bertrand

Reputation: 280644

I agree, DECIMAL is where you should store this type of number. But to make the decision easier, store it as a percentage of 1, not as a percentage of 100. That way you can store exactly the number of decimal places you need regardless of the "whole" number. So if you want 6 decimal places, use DECIMAL(9, 8) and for 23.3436435%, you store 0.23346435. Changing it to 23.346435% is a display problem, not a storage problem, and most presentation languages / report writers etc. are capable of changing the display for you.

Upvotes: 21

Shimmy Weitzhandler
Shimmy Weitzhandler

Reputation: 104841

I think decimal(p, s) should be used while s represents the percentage capability. the 'p' could of been even 1 since we will never need more than one byte since each digit in left side of the point is one hunderd percent, so the p must be at least s+1, in order you should be able to store up to 1000%. but SQL doesn't allow the 'p' to be smaller than the s.

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

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

And so on

Upvotes: 10

OMG Ponies
OMG Ponies

Reputation: 332791

The datatype of the column should be decimal.

Upvotes: 5

Related Questions