Single/Double v Decimal

[Astra]

Hi All

I know I'm supposed to sit in a corner with my test machines and mull over
what I think is the best, but I wanted your honest opinion on this.

Basically if I am storing decimal-based figures in my DB (actually MySQL,
but please don't let that put you off reading and replying to this), do you
prefer using single/double or decimal as the data type.

My case against Single/Double is that I created a simple TaxRate field to
hold 0.175 and my ASP query wouldn't pick it up even though a quick SELECT *
showed 0.175 in the field.

When I (finally) fathomed that I had to change my query to show '...
Round(TAXRATE,3) = 0.175' I found that the value would get picked up. I'm
presuming this is because the actual data is in fact 0.1750000000001 or
something.

When I changed my data type to decimal (10,3) my query worked fine without
the Round() command. I'm presuming this is the case because the value is
actually 0.175 and no afters.

My queries to you are:

a) If this is a common problem with Single/Double (float??) data types why
doesn't everybody use Decimal? Is Decimal fairly new or something?

b) Is Decimal(10,3) a good setting or do people set this to something like
Decimal(16,6) for accuracy and then chop it in their ASP?

Many thanks.

Rgds

Robbie

[Aaron [SQL Server MVP]]

> presuming this is because the actual data is in fact 0.1750000000001

I use the same argument against using FLOAT/REAL in SQL Server.

[url]http://www.aspfaq.com/2477[/url]
[url]http://www.aspfaq.com/2503[/url]

However, mathemeticians like Steve Kass certainly find useful places for
FLOAT. If you browse through these threads you will surely find some
instances where FLOAT is preferred over the "more precise" DECIMAL data
type. This involves SQL Server, but it seems like the issue is similar in
MySQL.

[url]http://groups.google.com/groups?q=author%3Akass+group%3A*sqlserver*+decimal +float&ie=UTF-8&hl=en[/url]

What precision / scale to use in the column type really depends on the
business needs. Let's assume, for starters, that you are storing prices.
Do you need 16 overall digits? If you are selling battleships, probably.
If you are selling 1/240 scale remote control battleships, probably not. Do
you need more than 3 decimal places? If you are selling tangible products,
you probably only need two decimal places, since very few products go more
granular than a penny. (If you are selling gasoline, you will need at least
3 decimal places, since the typical price is something.something and
9/10ths.) If you are selling shares on the stock market, you probably need
more decimal places. Also, if you are selling goods in other countries and
measuring in other currencies, this can affect your choice as well. If you
are selling in Japan or Mexico or Italy, you don't need decimal places for
yen or pesos or lira. But if you are selling in England, the conversion <->
US dollars will probably require more than 2 decimal places.

So, without having a single clue about what kind of data you are storing and
how it is going to be used, we are certainly in no place to even guess at
what choice will be optimal.

BTW, (16,6) vs (10,3) are different in more ways than simply the number of
decimal places.

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)

[Laphan]

Many thanks Aaron (once again).

Rgds

Robbie


Aaron [SQL Server MVP] <ten.xoc@dnartreb.noraa> wrote in message
news:uUwhhGdZEHA.3692@TK2MSFTNGP09.phx.gbl...

> presuming this is because the actual data is in fact 0.1750000000001

I use the same argument against using FLOAT/REAL in SQL Server.

[url]http://www.aspfaq.com/2477[/url]
[url]http://www.aspfaq.com/2503[/url]

However, mathemeticians like Steve Kass certainly find useful places for
FLOAT. If you browse through these threads you will surely find some
instances where FLOAT is preferred over the "more precise" DECIMAL data
type. This involves SQL Server, but it seems like the issue is similar in
MySQL.

[url]http://groups.google.com/groups?q=author%3Akass+group%3A*sqlserver*+decimal +[/url]
float&ie=UTF-8&hl=en

What precision / scale to use in the column type really depends on the
business needs. Let's assume, for starters, that you are storing prices.
Do you need 16 overall digits? If you are selling battleships, probably.
If you are selling 1/240 scale remote control battleships, probably not. Do
you need more than 3 decimal places? If you are selling tangible products,
you probably only need two decimal places, since very few products go more
granular than a penny. (If you are selling gasoline, you will need at least
3 decimal places, since the typical price is something.something and
9/10ths.) If you are selling shares on the stock market, you probably need
more decimal places. Also, if you are selling goods in other countries and
measuring in other currencies, this can affect your choice as well. If you
are selling in Japan or Mexico or Italy, you don't need decimal places for
yen or pesos or lira. But if you are selling in England, the conversion <->
US dollars will probably require more than 2 decimal places.

So, without having a single clue about what kind of data you are storing and
how it is going to be used, we are certainly in no place to even guess at
what choice will be optimal.

BTW, (16,6) vs (10,3) are different in more ways than simply the number of
decimal places.

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)