Appropriate datatype for holding percent values

Question

What is the best datatype for holding percent values ranging from 0 00  to 100 00

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  amp  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

User · Answer

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

[sql-server] Appropriate datatype for holding percent values?

Examples related to sql-server

Examples related to types

Examples related to sqldatatypes