SQL Server 2012 Integer Division Decimal Places

Question

I ran the following in SQL Server 2012

SELECT CONVERT(DECIMAL(7,2),4)/CONVERT(DECIMAL (7,2),3)

and got the following.

1.3333333333

I believe DECIMAL treats 7 as precision and 2 as scale, so shouldn't I get the result to 2 decimal places?

Answer 1

You need to convert the result to DECIMAL(7,2) too. For example:

SELECT CONVERT(DECIMAL(7,2), CONVERT(DECIMAL(7,2),4)/CONVERT(DECIMAL (7,2),3))

This will give you:

1.33

Answer 2

According to MSDN, when you divide e1/e2 which are decimal(p1, s1) and decimal(p2, s2),

resulting precision is p1 - s1 + s2 + max(6, s1 + p2 + 1)
resulting scale is max(6, s1 + p2 + 1)

So you get precision 7 - 2 + 2 + max(6, 2 + 2 + 1) = 13
And scale max(6, 2 + 7 + 1) = 10

for a result of decimal(13, 10)

The reason for this is that you would lose accuracy if you didn't expand the precision and scale depending on the operands: look at .5/2 = .25 and compare with .5/20 = .025. If you use the money type you will probably run into this sooner or later because that has a fixed four-digit 'scale'. For example: https://stackoverflow.com/a/582819/155892

https://msdn.microsoft.com/en-us/library/ms190476.aspx