Re: Precision and Scale innacuracies when dividing

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Steve Kass (skass_at_drew.edu)
Date: 08/24/04 

Date: Tue, 24 Aug 2004 00:51:57 -0500

Bri,

  The rules for typing the result of arithmetic on decimals are given in
Books Online, under the topic "Precision, Scale, and Length". They are not
intuitive, but there are reasons for them. SQL Server has to determine the
type of D1/D2 based on the types of D1 and D2 alone, not on their specific
values. It's not an easy job to figure out the best way to do this, since
there are 772 different decimal types, and therefore over half a million
combinations for which a result type must be defined! While for small
precision values, it works pretty well, but there have to be compromises
with high-precision types. Here's an example: a quotient of the form
decimal(30,25)/decimal(38,25) could be as large as 10^30
(99999.999.../0.000...001) and as small as 10^-38
(0.00001/9999999999999.99...). SQL Server favors accommodating the range of
results so types the result as decimal(38,8). That limits the accuracy of
small results, but avoids overflow. The A different choice could have been
made, but then more people would be posting in the newsgroup asking why
arithmetic with decimals causes overflow when it shouldn't.

In order to get the best possible accuracy, use types with no more precision
than you need, to avoid having SQL Server choose a result type that
accommodates values you will never actually generate. Sometimes it's even
better to use the so-called "approximate" data type float, and it can
sometimes help to cast intermediate results of a complicated expression.

One not-so-well documented issue along these lines is the typing of
aggregates, where things can get really crazy:

select sum(x)/10000.0 from (
  select 0.001 as x
) T

The problem here is that sum(x) gets typed as something like decimal(38,3) -
remember, the type of the result can't depend on the actual data, only on
the types, and the goals is to avoid having sum(x) overflow even for very
large tables. Then to further avoid overflow when dividing the sum by
10000.0, the scale goes to 6, because some effort is made to keep six
decimal places whenever possible, but unfortunately, this particular result
needs a higher scale to be expressed as a non-zero value...

Steve Kass
Drew University

"Bri Gipson" <spammers@not.welcome.com> wrote in message
news:v%yWc.31957$9Y6.8061@newsread1.news.pas.earthlink.net...
> Here's perhaps a better example. It shows that reducing the precision
> produces a more defined result. Notice how using a DECIMAL(30,25) produces
a
> result defined to the 23rd decimal place while using a DECIMAL(38,25) only
> produces a result to the 15th decimal place.
>
> Still the result is way off of the real value, as mentioned in the prior
> post.
>
> -Bri Gipson
>
> -- Code
>
> CREATE TABLE #values
> (
> dividend DECIMAL(20,10),
> divisor DECIMAL(20,10)
> )
>
> CREATE TABLE #bad_precision_a
> (
> set_name CHAR(1) DEFAULT 'A',
> quotient1 DECIMAL(33,15),
> quotient2 DECIMAL(38,25),
> quotient3 DECIMAL(26,25),
> quotient4 DECIMAL(25,20)
> )
>
> CREATE TABLE #bad_precision_b
> (
> set_name CHAR(1) DEFAULT 'B',
> quotient1 DECIMAL(33,15),
> quotient2 DECIMAL(38,25),
> quotient3 DECIMAL(26,25),
> quotient4 DECIMAL(25,20)
> )
>
> CREATE TABLE #bad_precision_c
> (
> set_name CHAR(1) DEFAULT 'C',
> quotient1 DECIMAL(33,15),
> quotient2 DECIMAL(38,25),
> quotient3 DECIMAL(26,25),
> quotient4 DECIMAL(25,20)
> )
>
> CREATE TABLE #bad_precision_d
> (
> set_name CHAR(1) DEFAULT 'D',
> quotient1 DECIMAL(33,15),
> quotient2 DECIMAL(38,25),
> quotient3 DECIMAL(26,25),
> quotient4 DECIMAL(25,20)
> )
>
> CREATE TABLE #bad_precision_e
> (
> set_name CHAR(1) DEFAULT 'E',
> quotient1 DECIMAL(33,15),
> quotient2 DECIMAL(38,25),
> quotient3 DECIMAL(26,25),
> quotient4 DECIMAL(25,20)
> )
>
> -- dividend / divisor = quotient
>
> /*
> INSERT INTO #values ( dividend, divisor )
> VALUES ( 0.0021, 0.0315 )
>
> INSERT INTO #values ( dividend, divisor )
> VALUES ( 0.000000073, 0.00042 )
> */
>
> INSERT INTO #values ( dividend, divisor )
> VALUES ( 0.0000000129381952, 90328.15713241413243 )
> -- The quotient should be: 0.00000000000014323546068844956882855010339423
>
> INSERT INTO #bad_precision_a
> ( quotient1, quotient2, quotient3, quotient4 )
> SELECT
> dividend / divisor,
> dividend / divisor,
> dividend / divisor,
> dividend / divisor
> FROM
> #values
>
> INSERT INTO #bad_precision_b
> ( quotient1, quotient2, quotient3, quotient4 )
> SELECT
> CONVERT( DECIMAL(38,25), dividend ) / CONVERT( DECIMAL(38,25),
divisor ),
> CONVERT( DECIMAL(38,25), dividend ) / CONVERT( DECIMAL(38,25),
divisor ),
> CONVERT( DECIMAL(38,25), dividend ) / CONVERT( DECIMAL(38,25),
divisor ),
> CONVERT( DECIMAL(38,25), dividend ) / CONVERT( DECIMAL(38,25), divisor )
> FROM
> #values
>
> INSERT INTO #bad_precision_c
> ( quotient1, quotient2, quotient3, quotient4 )
> SELECT
> CONVERT( DECIMAL(38,25), dividend ) / divisor,
> CONVERT( DECIMAL(38,25), dividend ) / divisor,
> CONVERT( DECIMAL(38,25), dividend ) / divisor,
> CONVERT( DECIMAL(38,25), dividend ) / divisor
> FROM
> #values
>
> INSERT INTO #bad_precision_d
> ( quotient1, quotient2, quotient3, quotient4 )
> SELECT
> CONVERT( DECIMAL(30,25), dividend ) / CONVERT( DECIMAL(30,25),
divisor ),
> CONVERT( DECIMAL(30,25), dividend ) / CONVERT( DECIMAL(30,25),
divisor ),
> CONVERT( DECIMAL(30,25), dividend ) / CONVERT( DECIMAL(30,25),
divisor ),
> CONVERT( DECIMAL(30,25), dividend ) / CONVERT( DECIMAL(30,25), divisor )
> FROM
> #values
>
> INSERT INTO #bad_precision_e
> ( quotient1, quotient2, quotient3, quotient4 )
> SELECT
> CONVERT( DECIMAL(30,25), dividend ) / divisor, -- 18,17
> CONVERT( DECIMAL(30,25), dividend ) / divisor, -- 18,17
> CONVERT( DECIMAL(30,25), dividend ) / divisor, -- 18,17
> CONVERT( DECIMAL(30,25), dividend ) / divisor -- 18,17
> FROM
> #values
>
> SELECT * FROM #values
>
> SELECT * FROM #bad_precision_a
> UNION
> SELECT * FROM #bad_precision_b
> UNION
> SELECT * FROM #bad_precision_c
> UNION
> SELECT * FROM #bad_precision_d
> UNION
> SELECT * FROM #bad_precision_e
>
> DROP TABLE #bad_precision_a
> DROP TABLE #bad_precision_b
> DROP TABLE #bad_precision_c
> DROP TABLE #bad_precision_d
> DROP TABLE #bad_precision_e
> DROP TABLE #values
>
>

Relevant Pages

  • Precision and Scale innacuracies when dividing
    ... When numbers of high precision are being divided they can come up completely ... INSERT INTO #bad_precision (dividend, divisor) ... -- The quotient should be: ...
    (microsoft.public.sqlserver.programming)
  • Re: Precision and Scale innacuracies when dividing
    ... It shows that reducing the precision ... INSERT INTO #values (dividend, divisor) ...
    (microsoft.public.sqlserver.programming)
  • Re: Really big math problem
    ... we mentally shifted the divisor (the number we ... digit of the dividend. ... We'd multiply the shifted divisor by that number, ...
    (microsoft.public.vc.mfc)
  • Re: Troubleshooting error in VB 6.0
    ... Chances are, you're reading data from a database into a recordset, and when going through the recordset, you're attempting to assign the field's value to a string variable or perhaps a textbox. ... Private Sub ShowRemainder(ByVal Divisor As Long, ByVal Dividend As Long) ...
    (microsoft.public.vb.general.discussion)
  • Re: division of two 4-bit vectors
    ... help, i am suposed to program a simple calculator in vhdl, and program ... repeatedly subtract the divisor from the dividend, ... DOULOS - Developing Design Know-how ...
    (comp.lang.vhdl)