Confiigure percent field on table

Question

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?

Answer 1

Hi,

You can use check constraint on a column..For more details on check constraint please have a look at below links

http://msdn.microsoft.com/en-us/library/ms188258(SQL.105).aspx

http://msdn.microsoft.com/en-us/library/ms179491(SQL.105).aspx

---

- Chintak (My Blog)

Answer 2

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);