Don't understand rounding behavior in sql serv

问题:

Could anybody explain what happens in the following sql code?

declare @dividend numeric(38,22)

declare @divisor numeric(38,22)

declare @otherDivisor int

set @dividend = 1

set @divisor = 3

set @otherDivisor = 3

select cast(@dividend / @divisor as numeric(38,22)), @dividend / @otherDivisor

The result returned is

---

0.3333330000000000000000 0.3333333333333333333333

I would expect the same result for both calculations.

回答1:

decimal(38,22) / decimal(38,22) ends up with decimal(x, 6) following these rules

So you have 0.33333 before you cast back to decimal(38,22)

@otherDivisor is cast to (38, 0) and stays as decimal(x,22)

See my worked example

回答2:

Try this:

select cast(@dividend as numeric(38,22)) / @divisor, @dividend / @otherDivisor

You are casting after doing the division.

回答3:

Actually I take that answer back. It looks as though SQL is coercing the result to whichever has the higher precedence, dividend or divisor.

      select 1.00000000000000000000/3.0
      select 1.0/3.0