# SQL percent field type

what field type should be used when dealing with percentages?

Alot of my percentages will be 1.3, instead of .013. However I will have numbers in there that way too

I am using a double type in my dataset for vb2005.
Anthony Perkins

membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
kenhaley

I would use numeric (9,4), and store ALL your percentages as decimals.  (Example:  4.3% is stored as .043).  Your input program should convert percentages to decimal before storing; often this is automatic if the user keys a % after the number.    In VB, or in other programming languages, if you display such a number using a percent format string, the decimal will be shifted appropriately, and display as 4.3%.  But the main advantage of storing as a raw decimal is that it works in math operations.  So, if you multiply the 4.3% (stored as .043) by, say \$200, you'd get \$8.60--the expected result.  But if you stored the percent column as 4.30, you'd get \$860.00 when you multiply the two values.

>>I would use numeric (9,4)<<
That would be a very large range for a percent, don't you agree?  I mean numeric(9,4) would give you a range of:
-99999.9999 to +99999.9999

Or am I missing something?

why would I not store it as a decimal?
>>why would I not store it as a decimal?<<
From BOL (in case you did not know):

<quote>
In Transact-SQL, numeric is functionally equivalent to the decimal data type.
</quote>

I am still not really sure after looking at this. Which one would I use and what type.

5,4 or 9,4,,decimal or numeric?

It does not matter if you call it numeric or decimal.  They are the same.
If you need 4 decimal places and one to the left of the decimal point than use 5, 4
If you need 4 decimal places and five to the left of the decimal point than use 9, 4

what if I have all my values be.

100% = .01
etc.

then what would it be??

I am going to make the change that all percentages are never greater then 100 so if it is 15% then it is.
.15, etc.

At least Numeric(3, 2)