Appropriate datatype for holding percent values?

sql-server types sqldatatypes
User picture User · May 4, 2010 · Viewed 160.2k times · Source

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

Answer

Thomas picture Thomas · May 4, 2010

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.

Related questions

Varchar columns: Nullable or not

The database development standards in our organization state the varchar fields should not allow null values. They should have a default value of an empty string (""). I know this makes querying and concatenation easier, but today, one of my coworkers …

Read More
Difference between numeric, float and decimal in SQL Server

What are the differences between numeric, float and decimal datatypes and which should be used in which situations? For any kind of financial transaction (e.g. for salary field), which one is preferred and why?

Read More
Is there a Boolean data type in Microsoft SQL Server like there is in MySQL?

Is there a Boolean data type in Microsoft SQL Server like there is in MySQL? If so, what is the alternative in MS SQL Server?

Read More