Sql Server Decimal(30,10) losing last 2 decimals

Question

When 2 decimal(30,10) numbers are divided in Sql Server 05, 2 last decimals seem to be getting lost (not even rounded off, simply truncated).

For example:

Declare @x decimal(30,10)
Declare @y decimal(30,10)
Declare @z decimal(30,10)

select @x = 2.1277164747 
select @y = 4.8553794574

Select @z = @y/@x   
select @z

Result: 2.28196731**00**

But if 2 numbers being divided are converted to float that seems to work:

....
Select @z = cast(@y as float)/cast(@x as float)
select @z

Result: 2.28196731**81**

Why is Sql doing this? And what's the right way of dividing decimals without loosing precision in Sql.

Answer 1

In short, use casting to guarantee your results. When you assign @x and @y to literal values, they are probably adopting the precision of those literals. This helps to explain why division by those values comes up short of your expectations.

Answer 2

The maximum precision allowed in SQL Server is 38. You are using Decimal(30,10). The max value is 99,999,999,999,999,999,999.9999999999 if you divide this number by 0.000000001, you will end up with an even bigger number, so the resulting data type must be able to accommodate it. This causes you to lose some precision.

Change your original data types to Decimal(20,10) and this problem does not occur.

For full rules regarding data types (and how they are affected by math operations):

Full rules here