sql server - Implicit conversions and rounding

Question

Just come across an interesting one:

declare @test as int
set @test = 47

select @test * 4.333

returns 203.651

declare @test as int
set @test = 47

declare @out as int
set @out = (select @test * 4.333)

select @out

returns 203

declare @test as int
set @test = 47

declare @out as int
set @out = round((select @test * 4.333),0)

select @out

returns 204

Now I know why it does this. Its because there is an implicit conversion from decimal to int, therefore the decimal places need chopped off (hence 203), whereas if I round prior to the implicit conversion I get 204.

My question is why when SQL Server does an implicit conversion is it not also rounding? I know if I had a big number, and it needed stored in a small place, the first thing I'd do would be to round it so as to be as close to the original number as possible.

It just doesn't seem intuitive to me.

Answer 1

This got me reading and the answer seems to be distinctly unsatisfying, The earliest SQL reference I've been able to find (ANSI 92 available here) in section 4.4.1 Characteristics of numbers states that

Whenever an exact or approximate numeric value is assigned to a data item or parameter representing an exact numeric value, an approximation of its value that preserves leading significant digits after rounding or truncating is represented in the data type of the target. The value is converted to have the precision and scale of the target. The choice of whether to truncate or round is implementation-defined.

Which leaves it up to Microsoft which of the two they chose to implement for T-SQL and I assume for the sake of simplicity they chose truncation. From the wikipedia article on rounding it seems that this wasn't an uncommon decision back in the day.

It's interesting to note that, according to the documentation I found, only conversions to integers cause truncation, the others cause rounding. Although for some bizarre reason the conversion from money to integer appears to buck the trend as it's allowed to round.

From     To       Behaviour

numeric  numeric  Round

numeric  int      Truncate

numeric  money    Round

money    int      Round

money    numeric  Round

float    int      Truncate

float    numeric  Round

float    datetime Round

datetime int      Round

Table from here.