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.

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.

ASKER CERTIFIED SOLUTION

membership

This content is only available to members.

To access this content, you must be a member of Experts Exchange.

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?

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?

ASKER

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>

From BOL (in case you did not know):

<quote>

In Transact-SQL, numeric is functionally equivalent to the decimal data type.

</quote>

ASKER

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?

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

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

ASKER

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.

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)

ASKER

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?

Would that be ok?

what are ur thoughts?

That is fine.