June 17, 2016 at 10:12 am
Run the following sql:
DECLARE @results TABLE(r INT, d DECIMAL(38,37))
DECLARE @X DECIMAL(3,2) = 1.00
INSERT INTO @results(r,d) VALUES(0, @X/7)
INSERT INTO @results(r,d) VALUES(1, @X/7.1);
INSERT INTO @results(r,d) VALUES(2, @X/7.01);
INSERT INTO @results(r,d) VALUES(3, @X/7.001);
INSERT INTO @results(r,d) VALUES(4, @X/7.0001);
INSERT INTO @results(r,d) VALUES(5, @X/7.00001);
INSERT INTO @results(r,d) VALUES(6, @X/7.000001);
INSERT INTO @results(r,d) VALUES(7, @X/7.0000001);
INSERT INTO @results(r,d) VALUES(8, @X/7.00000001);
INSERT INTO @results(r,d) VALUES(9, @X/7.000000001);
INSERT INTO @results(r,d) VALUES(10, @X/7.0000000001);
INSERT INTO @results(r,d) VALUES(11, @X/7.00000000001);
INSERT INTO @results(r,d) VALUES(12, @X/7.000000000001);
INSERT INTO @results(r,d) VALUES(13, @X/7.0000000000001);
INSERT INTO @results(r,d) VALUES(14, @X/7.00000000000001);
INSERT INTO @results(r,d) VALUES(15, @X/7.000000000000001);
INSERT INTO @results(r,d) VALUES(16, @X/7.0000000000000001);
INSERT INTO @results(r,d) VALUES(17, @X/7.00000000000000001);
INSERT INTO @results(r,d) VALUES(18, @X/7.000000000000000001);
INSERT INTO @results(r,d) VALUES(19, @X/7.0000000000000000001);
INSERT INTO @results(r,d) VALUES(20, @X/7.00000000000000000001);
INSERT INTO @results(r,d) VALUES(21, @X/7.000000000000000000001);
INSERT INTO @results(r,d) VALUES(22, @X/7.0000000000000000000001);
INSERT INTO @results(r,d) VALUES(23, @X/7.00000000000000000000001);
INSERT INTO @results(r,d) VALUES(24, @X/7.000000000000000000000001);
INSERT INTO @results(r,d) VALUES(25, @X/7.0000000000000000000000001);
INSERT INTO @results(r,d) VALUES(26, @X/7.00000000000000000000000001);
INSERT INTO @results(r,d) VALUES(27, @X/7.000000000000000000000000001);
INSERT INTO @results(r,d) VALUES(28, @X/7.0000000000000000000000000001);
INSERT INTO @results(r,d) VALUES(29, @X/7.00000000000000000000000000001);
INSERT INTO @results(r,d) VALUES(30, @X/7.000000000000000000000000000001);
INSERT INTO @results(r,d) VALUES(31, @X/7.0000000000000000000000000000001);
INSERT INTO @results(r,d) VALUES(32, @X/7.00000000000000000000000000000001);
INSERT INTO @results(r,d) VALUES(33, @X/7.000000000000000000000000000000001);
INSERT INTO @results(r,d) VALUES(34, @X/7.0000000000000000000000000000000001);
INSERT INTO @results(r,d) VALUES(35, @X/7.00000000000000000000000000000000001);
INSERT INTO @results(r,d) VALUES(36, @X/7.000000000000000000000000000000000001);
INSERT INTO @results(r,d) VALUES(37, @X/7.0000000000000000000000000000000000001);
The results are:
r...........d
0...........0.1442850000000000000000000000000000000
1...........0.1422530000000000000000000000000000000
2...........0.1440790000000000000000000000000000000
3...........0.1442651000000000000000000000000000000
4...........0.1442836500000000000000000000000000000
5...........0.1442855080000000000000000000000000000
6...........0.1442856936000000000000000000000000000
7...........0.1442857122200000000000000000000000000
8...........0.1442857140790000000000000000000000000
9...........0.1442857142651000000000000000000000000
10..........0.1442857142836500000000000000000000000
11..........0.1442857142855080000000000000000000000
12..........0.1442857142856936000000000000000000000
13..........0.1442857142857122200000000000000000000
14..........0.1442857142857140790000000000000000000
15..........0.1442857142857142651000000000000000000
16..........0.1442857142857142836500000000000000000
17..........0.1442857142857142855000000000000000000
18..........0.1442857142857142856000000000000000000
19..........0.1442857142857142850000000000000000000
20..........0.1442857142857142800000000000000000000
21..........0.1442857142857142000000000000000000000
22..........0.1442857142857140000000000000000000000
23..........0.1442857142857100000000000000000000000
24..........0.1442857142857000000000000000000000000
25..........0.1442857142850000000000000000000000000
26..........0.1442857142800000000000000000000000000
27..........0.1442857142000000000000000000000000000
28..........0.1442857140000000000000000000000000000
29..........0.1442857100000000000000000000000000000
30..........0.1442857000000000000000000000000000000
31..........0.1442850000000000000000000000000000000
32..........0.1442850000000000000000000000000000000
33..........0.1442850000000000000000000000000000000
34..........0.1442850000000000000000000000000000000
35..........0.1442850000000000000000000000000000000
36..........0.1442850000000000000000000000000000000
37..........0.1442850000000000000000000000000000000
Notice how the precision for the numbers used in the inserts is always increasing, but in the results the precision increases up to 20 in rows 16 and 17 and then goes down to 6.
Changing the precision of @X changes the point at which the precision in the results starts to decrease, but the pattern is always the same: it either starts at the max precision or it builds up to that max and then it goes down to 6 decimal places.
Why is that?
June 17, 2016 at 10:33 am
This page might help you understand what's happening. https://msdn.microsoft.com/en-us/library/ms190476.aspx
Especially the remark: "The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated."
This code might also help
DECLARE @results TABLE(r INT, d DECIMAL(38,37))
DECLARE @X DECIMAL(3,2) = 1.00
SELECT * INTO ##Test00 FROM( VALUES(0, @X/7))x(r,d)
SELECT * INTO ##Test01 FROM( VALUES(1, @X/7.1))x(r,d);
SELECT * INTO ##Test02 FROM( VALUES(2, @X/7.01))x(r,d);
SELECT * INTO ##Test03 FROM( VALUES(3, @X/7.001))x(r,d);
SELECT * INTO ##Test04 FROM( VALUES(4, @X/7.0001))x(r,d);
SELECT * INTO ##Test05 FROM( VALUES(5, @X/7.00001))x(r,d);
SELECT * INTO ##Test06 FROM( VALUES(6, @X/7.000001))x(r,d);
SELECT * INTO ##Test07 FROM( VALUES(7, @X/7.0000001))x(r,d);
SELECT * INTO ##Test08 FROM( VALUES(8, @X/7.00000001))x(r,d);
SELECT * INTO ##Test09 FROM( VALUES(9, @X/7.000000001))x(r,d);
SELECT * INTO ##Test10 FROM( VALUES(10, @X/7.0000000001))x(r,d);
SELECT * INTO ##Test11 FROM( VALUES(11, @X/7.00000000001))x(r,d);
SELECT * INTO ##Test12 FROM( VALUES(12, @X/7.000000000001))x(r,d);
SELECT * INTO ##Test13 FROM( VALUES(13, @X/7.0000000000001))x(r,d);
SELECT * INTO ##Test14 FROM( VALUES(14, @X/7.00000000000001))x(r,d);
SELECT * INTO ##Test15 FROM( VALUES(15, @X/7.000000000000001))x(r,d);
SELECT * INTO ##Test16 FROM( VALUES(16, @X/7.0000000000000001))x(r,d);
SELECT * INTO ##Test17 FROM( VALUES(17, @X/7.00000000000000001))x(r,d);
SELECT * INTO ##Test18 FROM( VALUES(18, @X/7.000000000000000001))x(r,d);
SELECT * INTO ##Test19 FROM( VALUES(19, @X/7.0000000000000000001))x(r,d);
SELECT * INTO ##Test20 FROM( VALUES(20, @X/7.00000000000000000001))x(r,d);
SELECT * INTO ##Test21 FROM( VALUES(21, @X/7.000000000000000000001))x(r,d);
SELECT * INTO ##Test22 FROM( VALUES(22, @X/7.0000000000000000000001))x(r,d);
SELECT * INTO ##Test23 FROM( VALUES(23, @X/7.00000000000000000000001))x(r,d);
SELECT * INTO ##Test24 FROM( VALUES(24, @X/7.000000000000000000000001))x(r,d);
SELECT * INTO ##Test25 FROM( VALUES(25, @X/7.0000000000000000000000001))x(r,d);
SELECT * INTO ##Test26 FROM( VALUES(26, @X/7.00000000000000000000000001))x(r,d);
SELECT * INTO ##Test27 FROM( VALUES(27, @X/7.000000000000000000000000001))x(r,d);
SELECT * INTO ##Test28 FROM( VALUES(28, @X/7.0000000000000000000000000001))x(r,d);
SELECT * INTO ##Test29 FROM( VALUES(29, @X/7.00000000000000000000000000001))x(r,d);
SELECT * INTO ##Test30 FROM( VALUES(30, @X/7.000000000000000000000000000001))x(r,d);
SELECT * INTO ##Test31 FROM( VALUES(31, @X/7.0000000000000000000000000000001))x(r,d);
SELECT * INTO ##Test32 FROM( VALUES(32, @X/7.00000000000000000000000000000001))x(r,d);
SELECT * INTO ##Test33 FROM( VALUES(33, @X/7.000000000000000000000000000000001))x(r,d);
SELECT * INTO ##Test34 FROM( VALUES(34, @X/7.0000000000000000000000000000000001))x(r,d);
SELECT * INTO ##Test35 FROM( VALUES(35, @X/7.00000000000000000000000000000000001))x(r,d);
SELECT * INTO ##Test36 FROM( VALUES(36, @X/7.000000000000000000000000000000000001))x(r,d);
SELECT * INTO ##Test37 FROM( VALUES(37, @X/7.0000000000000000000000000000000000001))x(r,d);
SELECT TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
NUMERIC_PRECISION,
NUMERIC_SCALE
FROM tempdb.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE '##Test%'
AND COLUMN_NAME = 'd'
ORDER BY TABLE_NAME;
GO
DROP TABLE ##Test00;
DROP TABLE ##Test01;
DROP TABLE ##Test02;
DROP TABLE ##Test03;
DROP TABLE ##Test04;
DROP TABLE ##Test05;
DROP TABLE ##Test06;
DROP TABLE ##Test07;
DROP TABLE ##Test08;
DROP TABLE ##Test09;
DROP TABLE ##Test10;
DROP TABLE ##Test11;
DROP TABLE ##Test12;
DROP TABLE ##Test13;
DROP TABLE ##Test14;
DROP TABLE ##Test15;
DROP TABLE ##Test16;
DROP TABLE ##Test17;
DROP TABLE ##Test18;
DROP TABLE ##Test19;
DROP TABLE ##Test20;
DROP TABLE ##Test21;
DROP TABLE ##Test22;
DROP TABLE ##Test23;
DROP TABLE ##Test24;
DROP TABLE ##Test25;
DROP TABLE ##Test26;
DROP TABLE ##Test27;
DROP TABLE ##Test28;
DROP TABLE ##Test29;
DROP TABLE ##Test30;
DROP TABLE ##Test31;
DROP TABLE ##Test32;
DROP TABLE ##Test33;
DROP TABLE ##Test34;
DROP TABLE ##Test35;
DROP TABLE ##Test36;
DROP TABLE ##Test37;
June 17, 2016 at 1:27 pm
I see it very clearly now, thanks!
June 17, 2016 at 1:49 pm
Nice example, Luis.
One thing which is unclear to me in all this is how SQL Server determines precision and scale for a given calculation. For example
1.00 / 7.1
which, according to my calculator, is
0.14084507042253521126760563380282 .....
Yet SQL Server calculates it as
0.1408450000000000000000000000000000000 (precision 8, scale 6)
We are nowhere near the 38 absolute maximum here. So where does the scale 6 come from?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 17, 2016 at 1:59 pm
It's using the formula shown on BOL.
Considering example 1.00 / 7.1 we just get the formulas.
p = p1 - s1 + s2 + max(6, s1 + p2 + 1)
s = max(6, s1 + p2 + 1)
We get the values:
p1=3 , s1=2
p2=2 , s2=1
We just need to apply the formula
p = 3 - 2 + 1 + max(6, 2 + 1 + 1)
s = max(6, 2 + 1 + 1)
June 17, 2016 at 2:03 pm
Luis Cazares (6/17/2016)
It's using the formula shown on BOL.Considering example 1.00 / 7.1 we just get the formulas.
p = p1 - s1 + s2 + max(6, s1 + p2 + 1)
s = max(6, s1 + p2 + 1)
We get the values:
p1=3 , s1=2
p2=2 , s2=1
We just need to apply the formula
p = 3 - 2 + 1 + max(6, 2 + 1 + 1)
s = max(6, 2 + 1 + 1)
I should have read through the link more closely; apologies :blush:
That is clear now, thank you.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 17, 2016 at 2:14 pm
Here's the formula applied for all values.
WITH CTE AS(
SELECT *,
p1 - s1 + s2 + CASE WHEN 6 > s1 + p2 + 1 THEN 6 ELSE s1 + p2 + 1 END p,
CASE WHEN 6 > s1 + p2 + 1 THEN 6 ELSE s1 + p2 + 1 END s
FROM (VALUES(3,2,01,00),
(3,2,02,01),
(3,2,03,02),
(3,2,04,03),
(3,2,05,04),
(3,2,06,05),
(3,2,07,06),
(3,2,08,07),
(3,2,09,08),
(3,2,10,09),
(3,2,11,10),
(3,2,12,11),
(3,2,13,12),
(3,2,14,13),
(3,2,15,14),
(3,2,16,15),
(3,2,17,16),
(3,2,18,17),
(3,2,19,18),
(3,2,20,19),
(3,2,21,20),
(3,2,22,21),
(3,2,23,22),
(3,2,24,23),
(3,2,25,24),
(3,2,26,25),
(3,2,27,26),
(3,2,28,27),
(3,2,29,28),
(3,2,30,29),
(3,2,31,30),
(3,2,32,31),
(3,2,33,32),
(3,2,34,33),
(3,2,35,34),
(3,2,36,35),
(3,2,37,36),
(3,2,38,37))x(p1,s1,p2,s2)
)
SELECT
p1,
s1,
p2,
s2,
CASE WHEN p > 38 THEN 38
ELSE p END p,
CASE WHEN p > 38 AND s - (p-38) >= 6 THEN s - (p-38)
WHEN p > 38 AND s - (p-38) < 6 THEN 6
ELSE s END s
FROM CTE;
June 20, 2016 at 10:54 pm
Phil Parkin (6/17/2016)
Nice example, Luis.One thing which is unclear to me in all this is how SQL Server determines precision and scale for a given calculation. For example
1.00 / 7.1
which, according to my calculator, is
0.14084507042253521126760563380282 .....
Yet SQL Server calculates it as
0.1408450000000000000000000000000000000 (precision 8, scale 6)
We are nowhere near the 38 absolute maximum here. So where does the scale 6 come from?
It's because of the precision you defined for the source values.
Indicate that more digits are known in those numbers and you'll get better precision in the output:
SELECT 1.0000000000000000000000000000000000000 / 7.1
returns:
0.140845070422535211267605633802816901
_____________
Code for TallyGenerator
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply