Search code examples
sql-servertypessqldatatypes

Appropriate datatype for holding percent values?

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

Solution

  • 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.

    Edit: Per modern conventions, we don't name columns indicating the type since the type may change one day and we use contracts and versions to make that indication. Hungarian naming had it's day, and we've moved on to better practices.