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

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
How to store decimal values in SQL Server?

I'm trying to figure out decimal data type of a column in the SQL Server. I need to be able to store values like 15.5, 26.9, 24.7, 9.8, etc I assigned decimal(18, 0) to the column data type but this not allowing me to store …

Read More