CODEWITHSUNDEEP
sqlsql-serverdecimal
CiucaS
CiucaS

Reputation: 2128

SQL Server Decimal precision

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.

Upvotes: 5

Views: 7732

Answers (3)

How 'bout a Fresca
How 'bout a Fresca

Reputation: 2317

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 :))

Upvotes: 1

Gouri Shankar Aechoor
Gouri Shankar Aechoor

Reputation: 1581

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)

Upvotes: 2

Jayvee
Jayvee

Reputation: 10875

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.

Upvotes: 4

Related Questions