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.2819673100

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.2819673181

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

Answer 1

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