Wednesday, August 29, 2012 9:37 AM
What is the ideal way to code a percentage field on a table?
I have a PROJECTS table and want to add a field called PERCENTCOMPLETE. I was thinking of just setting it as an int. How can I limit it to a number between 0 and 100?
Wednesday, August 29, 2012 9:40 AM
You can use check constraint on a column..For more details on check constraint please have a look at below links
- Chintak (My Blog)
Wednesday, August 29, 2012 12:13 PMModerator
I agree with Chintak: Use a check contraint. Another thing to consider is to use a tinyint datatype in this case. There are a couple of advantages to the tinyint in this case:
- The tinyint uses 1 byte of storage rather than 4
- The check constraint for the tinyint is slightly simpler than the check constraint for the integer
The alter for the integer:
alter table projects add percentComplete integer constraint ck_PercentComplete check(percentComplete between 0 and 100);
For the tinyint:
alter table projects add percentComplete tinyint constraint ck_PercentComplete check(percentComplete <= 100);