Decimal vs. Float Data Type

I've been looking at the Decimal and Float data types. When it come to
storing a value such as 10.55%, 25.00% or 99.99%, which is the most
appropriate?I recommend decimal instead of float if you need exact decimal values .
Float and real follow the IEEE 754 specification on approximate numeric data
types and cannot store all decimal values accurately. See "approximate data
types" in the the Books Online for details.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Greg" <AccessVBAnet@.newsgroups.nospam> wrote in message
news:43D7259F-B754-4753-AB5B-85CEABDA319F@.microsoft.com...
> I've been looking at the Decimal and Float data types. When it come to
> storing a value such as 10.55%, 25.00% or 99.99%, which is the most
> appropriate?|||So in this case, 10.55%, 100.99%, etc. I would use Decimal(5.2), Precision =5, Scale = 2?
Thanks
"Dan Guzman" wrote:
> I recommend decimal instead of float if you need exact decimal values .
> Float and real follow the IEEE 754 specification on approximate numeric data
> types and cannot store all decimal values accurately. See "approximate data
> types" in the the Books Online for details.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Greg" <AccessVBAnet@.newsgroups.nospam> wrote in message
> news:43D7259F-B754-4753-AB5B-85CEABDA319F@.microsoft.com...
> > I've been looking at the Decimal and Float data types. When it come to
> > storing a value such as 10.55%, 25.00% or 99.99%, which is the most
> > appropriate?
>|||> So in this case, 10.55%, 100.99%, etc. I would use Decimal(5.2), Precision
> => 5, Scale = 2?
It depends on the range you need to store. Decimal(5,2) is sufficient to
store -999.99 through +999.99.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Greg" <AccessVBAnet@.newsgroups.nospam> wrote in message
news:D535955C-A6AF-4C3B-B689-22BEC7D84A44@.microsoft.com...
> So in this case, 10.55%, 100.99%, etc. I would use Decimal(5.2), Precision
> => 5, Scale = 2?
> Thanks
>
> "Dan Guzman" wrote:
>> I recommend decimal instead of float if you need exact decimal values .
>> Float and real follow the IEEE 754 specification on approximate numeric
>> data
>> types and cannot store all decimal values accurately. See "approximate
>> data
>> types" in the the Books Online for details.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Greg" <AccessVBAnet@.newsgroups.nospam> wrote in message
>> news:43D7259F-B754-4753-AB5B-85CEABDA319F@.microsoft.com...
>> > I've been looking at the Decimal and Float data types. When it come to
>> > storing a value such as 10.55%, 25.00% or 99.99%, which is the most
>> > appropriate?|||I actually had this doubt as well for a long time. The number of bytes that
each datatype use does not make much difference (check BOL). So when I
require exactness and it comes to numbers with not many decimal positions
and depending on the operation (which in some cases can make the exact
numeric data types behave not so exact), I definitely prefer decimal data
type. In some other cases the float can be useful.
I think you can read Hugo Kornelis' article 'So-called "exact" numerics are
not at all exact!' on SQLblog.com, which helped me a lot to understand
better when to use each datatype.
Alan Ferrandiz Langley [MCT]
"Greg" <AccessVBAnet@.newsgroups.nospam> escribió en el mensaje
news:43D7259F-B754-4753-AB5B-85CEABDA319F@.microsoft.com...
> I've been looking at the Decimal and Float data types. When it come to
> storing a value such as 10.55%, 25.00% or 99.99%, which is the most
> appropriate?
>|||On Mon, 28 Jan 2008 01:47:39 -0500, Alan Ferrandiz Langley [MCT] wrote:
(snip)
>I think you can read Hugo Kornelis' article 'So-called "exact" numerics are
>not at all exact!' on SQLblog.com, which helped me a lot to understand
>better when to use each datatype.
Hi Alan,
Thanks for the free plug, and the nice words. For anyone interested,
here is a direct link to that article:
http://sqlblog.com/blogs/hugo_kornelis/archive/2007/10/17/so-called-exact-numerics-are-not-at-all-exact.aspx
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Decimal field is rounding up my numbers

Hi,

I have a decimal field in SQL Server 2000 which has a precision value of 3 and scale 1. I will be storing values ranging from 0.5 to 10.0 in there. However, in my asp.net web form, if I select the value 2.5 from the DropDownList, SQL Server stores it as 3.

Can anyone tell me why this is happening and give me some pointers on what I can do to fix it? Your help is much appreciated.

how are you passing the values from the asp.net page? whats the datatype? can you post the asp.net code? also if you manually insert the values from query analyzer does it insert the right values?

|||

Hi,

I entered 2.5 using QueryAnalyzer and SQL Server stored it as 2. So the issue is with the way I defined this Decimal field in SQL Server. The problem is I don't see where I'm making a mistake. If I set the precision to 3 and scale to 1, I should be able to have values ranging from 0.5 to 10.0 in there, should I?

|||your scale is right. try increasing your precision to 5. also check if you are working with the right column.|||Nope... I enter 3.5, it stores it as 4.0. And I'm doing this Query Analyzer so there's no question about where the data is going.|||

SamU wrote:

Nope... I enter 3.5, it stores it as 4.0. And I'm doing this Query Analyzer so there's no question about where the data is going.

I have added a testDecimal column to my Test table, and set it up as a decimal field with a precision of 3 and a scale of 1, and I am not seeing this odd behavior. It is storing the data exactly as I supply it (3.5, .5, 10.0, etc.) Please explain how exactly you are "doing this in Query Analyzer" as QA does not give you the facility to directly update the data; you need to execute a query to do this. I used queries like this:

UPDATE test SET testDecimal = 3.5 WHERE ID = 4
SELECT testDecimal FROM test WHERE testDecimal IS NOT NULL

For reference, when I first added the testDecimal column through Enterprise Manager, the default length was 9, the precision was 18, and the scale was 0. I changed the precision to 3 and the scale to 1, and the length automatically changed to 5.|||

We may be narrowing this down. I'm actually inserting new values into a table and I'm doing this through a stored procedure. The parameter that inserts the value is defined as decimal. I'm including the code down below. Do I need any additional values that further define the parameter's precision and scale in the stored procedure? Looks like it's the stored procedure that's rounding the number up, not the table. Can anyone see an issue w/ this stored procedure?

Here's the Stored Procedure code. The parameter that inserts value into this field is @.JobLength.

ALTER PROCEDURE dbo.spTalentReleaseNew
(
@.EmployeeID smallint,
@.JobName varchar(200),
@.JobDate smalldatetime,
@.DealID int,
@.JobLength decimal,
@.TalentAgencyName varchar(200) = null,
@.TalentID int,
@.TalentRate smallmoney,
@.LocationRate smallmoney,
@.MakeUpRate smallmoney,
@.FoodStylistRate smallmoney,
@.LastUpdateTimeStamp datetime
)
AS
/* ObjectID = 221; This stored procedure creates a new Talent Release. */
INSERT INTO tblTalentRelease
(EmployeeID, JobName, JobDate, DealID,JobLength, TalentAgencyName, TalentID, TalentRate, LocationRate, MakeUpRate, FoodStylistRate,
LastUpdateTimeStamp, LastUpdatedBy)
VALUES (@.EmployeeID, @.JobName, @.JobDate, @.DealID,@.JobLength, @.TalentAgencyName, @.TalentID, @.TalentRate, @.LocationRate, @.MakeUpRate,
@.FoodStylistRate, @.LastUpdateTimeStamp, @.EmployeeID)

|||That's the problem. If you just declare the @.JobLength as decimal, it is created with a scale equal to zero as the default (and precision of 18). Set it like this instead:

@.JobLength decimal(3, 1)

and you should be fine.

Don|||THanks Don.