I'm
about difference between Decimal and Float data types , if imwriting accounting application and use 10:4 as precision/scale in all
numbers , does it matters if i choose fields as Decimal or Float in that
case ?
in BOL its says about float that
Approximate number data types for use with floating point numeric data.
Floating point data is approximate; not all values in the data type range
can be precisely represented.
i do not know what that means? can anyone give small example adding 2
different numbers that will give different answers if column type is decimal
than float ?
Best Regards
Bassamfloats store numbers as base 2, decimals as base 10. You can lookup a full
explaination on google, I would not do it justice. Tey this to see how they
differ.
create table Test
(NumDecimal decimal(10,4)
, numFloat float
);
insert into Test (NumDecimal, numFloat) values (0.1, 0.1);
insert into Test (NumDecimal, numFloat) values (0.3, 0.3);
insert into Test (NumDecimal, numFloat) values (0.25, 0.25);
insert into Test (NumDecimal, numFloat) values (1.0/3.0, 1.0/3.0);
insert into Test (NumDecimal, numFloat) values (1.0/6.0, 1.0/6.0);
select * from test;
select numdecimal*3 , numfloat*3 from test;
drop table Test;
"Bassam" <bassam@.nptco.com.eg> wrote in message
news:OpLEDirbGHA.2456@.TK2MSFTNGP04.phx.gbl...
> Hello, sorry if this question is silly
> I'm
about difference between Decimal and Float data types , if im> writing accounting application and use 10:4 as precision/scale in all
> numbers , does it matters if i choose fields as Decimal or Float in that
> case ?
> in BOL its says about float that
> Approximate number data types for use with floating point numeric data.
> Floating point data is approximate; not all values in the data type range
> can be precisely represented.
> i do not know what that means? can anyone give small example adding 2
> different numbers that will give different answers if column type is
decimal
> than float ?
> --
> Best Regards
> Bassam
>
>|||> can anyone give small example adding 2
> different numbers that will give different answers if column type is decim
al
> than float ?
Run below in Query Analyzer and you will see:
DECLARE @.fa float, @.fb float, @.da decimal(10,4), @.db decimal(10,4)
SELECT @.fa = 3.1, @.da = 3.1
SELECT @.fb = 5.5, @.db = 5.5
SELECT @.fa + @.fb
SELECT @.da + @.db
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bassam" <bassam@.nptco.com.eg> wrote in message news:OpLEDirbGHA.2456@.TK2MSFTNGP04.phx.gbl.
.
> Hello, sorry if this question is silly
> I'm
about difference between Decimal and Float data types , if im> writing accounting application and use 10:4 as precision/scale in all
> numbers , does it matters if i choose fields as Decimal or Float in that
> case ?
> in BOL its says about float that
> Approximate number data types for use with floating point numeric data.
> Floating point data is approximate; not all values in the data type range
> can be precisely represented.
> i do not know what that means? can anyone give small example adding 2
> different numbers that will give different answers if column type is decim
al
> than float ?
> --
> Best Regards
> Bassam
>
>|||I'm not sure you'll get an example by adding 2 numbers, but an
important point to note is that floats will sometimes "miss by a bit",
so you'll find that if you're doing lots of division and multiplication
you might end up with 1.00000000016 rather than 1. Generally your
application will have a natural degree of accuracy which you're working
within, so decimals are much better to use as they will not cause this
kind of behaviour|||Bassam (bassam@.nptco.com.eg) writes:
> Hello, sorry if this question is silly
> I'm
about difference between Decimal and Float data types , if im> writing accounting application and use 10:4 as precision/scale in all
> numbers , does it matters if i choose fields as Decimal or Float in that
> case ?
> in BOL its says about float that
> Approximate number data types for use with floating point numeric data.
> Floating point data is approximate; not all values in the data type range
> can be precisely represented.
> i do not know what that means? can anyone give small example adding 2
> different numbers that will give different answers if column type is
> decimal than float ?
Run this in Query Analyzer:
declare @.d1 decimal(10, 4), @.d2 decimal(10, 4),
@.f1 float, @.f2 float
SELECT @.d1 = 98.234, @.d2 = 87.0987
SELECT @.f1 = 98.234, @.f2 = 87.0987
SELECT @.d1 = 98.234, @.d2 = 87.0987
SELECT @.d1 + @.d2, @.f1 + @.f2
More generally, while is valid and reasonable to write:
WHERE decimalcol = 0
the same is not true for
WHERE floatcal = 0
Because due to rounding errors, floatcol may have a value like
0.0000000000000123
It's possible to use float in an accounting application, but you have to
be very careful. Decimal has its pitfalls too, but is probably safer.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||In an accounting application, exact values (decimal data type) should be
used.
For float and real data types, they are approximate as it a the specified
number of bits to store the mantissa of the float number in scientific
notation, in binary form. The conversion of a decimal value (with decimal
places) to a binary value usually results in a lost of precision.
Try the following.
select convert(decimal(10, 4), 111.1111) as MyDecimalValue,
convert(float(24), 111.1111) as MyFloatValue
Martin C K Poon
Senior Analyst Programmer
====================================
"Bassam" <bassam@.nptco.com.eg> bl
news:OpLEDirbGHA.2456@.TK2MSFTNGP04.phx.gbl g...
> Hello, sorry if this question is silly
> I'm
about difference between Decimal and Float data types , if im> writing accounting application and use 10:4 as precision/scale in all
> numbers , does it matters if i choose fields as Decimal or Float in that
> case ?
> in BOL its says about float that
> Approximate number data types for use with floating point numeric data.
> Floating point data is approximate; not all values in the data type range
> can be precisely represented.
> i do not know what that means? can anyone give small example adding 2
> different numbers that will give different answers if column type is
decimal
> than float ?
> --
> Best Regards
> Bassam
>
>|||you know, when running this in Management Studio I get
@.fa + @.fb = 8.6
@.da + @.db = 8.600
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23VAvd2rbGHA.2396@.TK2MSFTNGP02.phx.gbl...
>
> Run below in Query Analyzer and you will see:
> DECLARE @.fa float, @.fb float, @.da decimal(10,4), @.db decimal(10,4)
> SELECT @.fa = 3.1, @.da = 3.1
> SELECT @.fb = 5.5, @.db = 5.5
> SELECT @.fa + @.fb
> SELECT @.da + @.db
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Bassam" <bassam@.nptco.com.eg> wrote in message
> news:OpLEDirbGHA.2456@.TK2MSFTNGP04.phx.gbl...|||Presenting the values returned (in binary format) from SQL Server is the tas
k of the client
application. Apparently, SSMS assumes that you aren't that concerned about a
ll the decimals when you
use float and real, while QA is more exact in the representation of these va
lues.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve" <ss@.Mailinator.com> wrote in message news:ufjgO$rbGHA.3800@.TK2MSFTNGP04.phx.gbl...[
color=darkred]
> you know, when running this in Management Studio I get
> @.fa + @.fb = 8.6
> @.da + @.db = 8.600
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%23VAvd2rbGHA.2396@.TK2MSFTNGP02.phx.gbl...
>[/color]|||For completeness... what are the pitfalls of the Decimal datatype? I've
always found that as long as I'm careful with the precision the results
are accurate.|||I'd rather have the results from SSMS include all the decimals. Anyway to
force that, or is there an option to change?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OAme4FsbGHA.3388@.TK2MSFTNGP05.phx.gbl...
> Presenting the values returned (in binary format) from SQL Server is the
> task of the client application. Apparently, SSMS assumes that you aren't
> that concerned about all the decimals when you use float and real, while
> QA is more exact in the representation of these values.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Steve" <ss@.Mailinator.com> wrote in message
> news:ufjgO$rbGHA.3800@.TK2MSFTNGP04.phx.gbl...
>sql
没有评论:
发表评论