Re: 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:56:11 GMT

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

  • 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: 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)
  • Re: DIV overflow
    ... word divisor;if you want a 16-bit divisor ... MOV eax, dword dividend ...
    (alt.lang.asm)