问题:

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.

回答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

回答2:

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.