Link to home
Get AccessLog in
Avatar of bman9111

asked on

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.
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
Avatar of 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?
Avatar of bman9111


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

In Transact-SQL, numeric is functionally equivalent to the decimal data type.
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

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)
I ended up using 4,2 decimal only because for a dataset for vs2005 only has decimal or double to choose from and didn't know which to use.

Would that be ok?
what are ur thoughts?
That is fine.