Precision and Scale innacuracies when dividing

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

From: Bri Gipson (spammers_at_not.welcome.com)
Date: 08/24/04 

Date: Tue, 24 Aug 2004 03:27:26 GMT

Hello,

I'm a bit confused as to what's been going on with the math in SQL Server.
When numbers of high precision are being divided they can come up completely
innacurate from the true (rounded) result. Why is that?

For example, in the following, I should receive something along the lines
of:
0.00000000000014323546
Instead I either get zero (which later turns into divide by zero errors) or
0.00000000000014281261

Furthermore, the scale tends to get truncated prematurely and padded with
zeros. I noticed in some cases that the smaller the precision the further
out the scale would go before padding. I wasn't able to duplicate that with
this test case, but it just doesn't make any sense why precision should
affect the result in the scale.

Why is that?
- Bri Gipson

-- Test case follows

CREATE TABLE #bad_precision
(
  quotient1a DECIMAL(33,15),
  quotient1b DECIMAL(33,15),
  quotient1c DECIMAL(33,15),
  quotient1d DECIMAL(33,15),
  quotient1e DECIMAL(33,15),
  quotient2a DECIMAL(38,25),
  quotient2b DECIMAL(38,25),
  quotient2c DECIMAL(38,25),
  quotient2d DECIMAL(38,25),
  quotient2e DECIMAL(38,25),
  quotient3a DECIMAL(26,25),
  quotient3b DECIMAL(26,25),
  quotient3c DECIMAL(26,25),
  quotient3d DECIMAL(26,25),
  quotient3e DECIMAL(26,25),
  quotient4a DECIMAL(25,20),
  quotient4b DECIMAL(25,20),
  quotient4c DECIMAL(25,20),
  quotient4d DECIMAL(25,20),
  quotient4e DECIMAL(25,20),
  dividend DECIMAL(20,10),
  divisor DECIMAL(20,10)
)

-- dividend / divisor = quotient

/*
INSERT INTO #bad_precision ( dividend, divisor )
VALUES ( 0.0021, 0.0315 )

INSERT INTO #bad_precision ( dividend, divisor )
VALUES ( 0.000000073, 0.00042 )
*/

INSERT INTO #bad_precision ( dividend, divisor )
VALUES ( 0.0000000129381952, 90328.15713241413243 )
-- The quotient should be: 0.00000000000014323546068844956882855010339423

UPDATE #bad_precision
SET
  quotient1a = dividend / divisor,
  quotient1b = CONVERT( DECIMAL(38,25), dividend ) / CONVERT(
DECIMAL(38,25), divisor ),
  quotient1c = CONVERT( DECIMAL(38,25), dividend ) / divisor,
  quotient1d = CONVERT( DECIMAL(30,25), dividend ) / CONVERT(
DECIMAL(30,25), divisor ),
  quotient1e = CONVERT( DECIMAL(30,25), dividend ) / divisor,
  quotient2a = dividend / divisor,
  quotient2b = CONVERT( DECIMAL(38,25), dividend ) / CONVERT(
DECIMAL(38,25), divisor ),
  quotient2c = CONVERT( DECIMAL(38,25), dividend ) / divisor,
  quotient2d = CONVERT( DECIMAL(30,25), dividend ) / CONVERT(
DECIMAL(30,25), divisor ),
  quotient2e = CONVERT( DECIMAL(30,25), dividend ) / divisor,
  quotient3a = dividend / divisor,
  quotient3b = CONVERT( DECIMAL(38,25), dividend ) / CONVERT(
DECIMAL(38,25), divisor ),
  quotient3c = CONVERT( DECIMAL(38,25), dividend ) / divisor,
  quotient3d = CONVERT( DECIMAL(30,25), dividend ) / CONVERT(
DECIMAL(30,25), divisor ),
  quotient3e = CONVERT( DECIMAL(30,25), dividend ) / divisor,
  quotient4a = dividend / divisor,
  quotient4b = CONVERT( DECIMAL(38,25), dividend ) / CONVERT(
DECIMAL(38,25), divisor ),
  quotient4c = CONVERT( DECIMAL(38,25), dividend ) / divisor,
  quotient4d = CONVERT( DECIMAL(18,17), dividend ) / CONVERT(
DECIMAL(22,17), divisor ),
  quotient4e = CONVERT( DECIMAL(18,17), dividend ) / divisor

SELECT * FROM #bad_precision

Relevant Pages

  • Re: Precision and Scale innacuracies when dividing
    ... Books Online, under the topic "Precision, Scale, and Length". ... arithmetic with decimals causes overflow when it shouldn't. ... > dividend DECIMAL, ... > divisor DECIMAL ...
    (microsoft.public.sqlserver.programming)
  • Re: Im officially embarrased
    ... the dividend and which is the devisor: Is the first number, ... and b is the divisor - although I ... leaving a whole number as remainder. ... For instant the quotient on dividing 30 by 7 is 4, ...
    (sci.math)
  • 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: If you were inventing CoBOL...
    ... > relationship among the descriptions of the divisor, the dividend and the ... > quotient. ... In the general case your suggestion wouldn't produce meaningful ...
    (comp.lang.cobol)