SQL Server Decimal precision

Question

declare @nr1 decimal(20,19),
        @nr2 decimal(20,19)
set @nr1 = EXP(1.0)
set @nr2 = PI();
print @nr1/@nr2

As EXP and PI are "infinite" numbers you should always have enough decimals to print

The result for this query is 0.865255979432265082

For the query :

declare @nr12 decimal(34,25),
        @nr22 decimal(34,25)
set @nr12 = EXP(1.0)
set @nr22 = PI();
print @nr12/@nr22

I get the result : 0.865255

So my question is, why is the first query more precise then the second one? As decimal(p,s) as it is define in msdn tells me that the second query should be more precise.

may be it subtracts the decimal(10,5) like 10 - 5 from right of the point to left of the point @ciucas — mohan111, Aug 01 '14 at 14:07
If you SELECT your variables before the division... you can see the latter ARE indeed of a larger scale/precision (albeit not fully populated), its the division operation that is truncating the result. — Dave C, Aug 01 '14 at 14:10
It's the other way around, @mohan111. Decimal(p,s) is defined as precision and scale, where precision is the maximum total number of digits stored and scale is the maximum number of digits to the right of the decimal that can be represented, up to a max of p. — dodexahedron, Aug 01 '14 at 14:10
@CiucaS, what happens if you set your precision to 38? You're already at 34, so you're already using 17 bytes to store it. 38 doesn't use any additional, per http://msdn.microsoft.com/en-us/library/ms187746.aspx — dodexahedron, Aug 01 '14 at 14:12

score 4 · Accepted Answer · answered Aug 01 '14 at 14:24

This link will help: http://msdn.microsoft.com/en-us/library/ms190476.aspx

according to this the scale of the result of a division e1/e2 will be given by this formula max(6, s1 + p2 + 1) and it also includes this note:

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.

probably you will be better using decimal(19,16) given that the scale for exp() and pi() are 16 in both cases.

Nice! I just stumbled on this: http://www.sqlservercentral.com/Forums/Topic870098-338-1.aspx the last post gives an example. — Dave C, Aug 01 '14 at 14:28

score 2 · Answer 2 · edited May 23 '17 at 10:33

There is a great explanation here T-SQL Decimal Division Accuracy

--Precision = p1 - s1 + s2 + max(6, s1 + p2 + 1)
--Scale = max(6, s1 + p2 + 1)

--Scale = 15 + 38 + 1 = 54
--Precision = 30 - 15 + 9 + 54 = 72
--Max P = 38, P & S are linked, so (72,54) -> (38,20)
--So, we have 38,20 output (but we don use 20 d.p. for this sum) = 11.74438969709659
SELECT CONVERT(DECIMAL(30,15),146804871.212533)/CONVERT(DECIMAL (38,9),12499999.9999)


--Scale = 15 + 38 + 1 = 54
--Precision = 30 - 15 + 15 + 54 = 84
--Max P = 38, P & S are linked, so (84,54) -> (38,8)
--So, we have 38,8 output = 11.74438969
SELECT CONVERT(DECIMAL(30,15),146804871.212533)/CONVERT(DECIMAL (38,15),12499999.9999)

score 1 · Answer 3 · answered Aug 01 '14 at 14:35

On technet you can see how precision is carried over or truncated here: http://msdn.microsoft.com/en-us/library/ms190476(v=sql.105).aspx

In summary (paraphrased from technet): When you have two decimal numbers e1 & e2, with scale s1 & s2 and precision p1 & p2, dividing e1/e2 gets you:

Result Precision: p1 - s1 + s2 + max(6, s1 + p2 + 1) Result Scale: max(6, s1 + p2 + 1)

So your second block has p1 = p2 = 34 and s1 = s2 = 25. The formula above will yield a result precision of

 34-25+25+max(6, 34+25+1)
=34+max(6,60)
=94

When the result precision is greater than 38, the scale is reduced (in a nutshell...the technet post is pretty good :))

SQL Server Decimal precision

3 Answers3