SQL str() vs round() function

Question

While working with str() function I found that in some cases it rounds wrong while round() function works as expected. Please look at the example:

declare @v decimal(18,2) = 29.95
select str(@v, 18, 1)
--29.9
select round(@v, 1)
--30.00

set @v = 39.95
select str(@v, 18, 1)
--40.00
select round(@v, 1)
--40.00

Can anyone explain why it happens?

EDIT1: I tested different workarounds with the following base code:

declare @v decimal(18,2) = 9.95
declare @r varchar(100)
declare @c int = 1000000
declare @ms int
declare @dt datetime2

set @dt = sysdatetime()

while @c > 0
begin
    set @r = --different roundings
    set @c = @c - 1
end

set @ms = DATEDIFF(ms, @dt, sysdatetime())
select @ms, @r

Option 1 (the original one, rounds wrongly in some cases):

str(@v, 18, 1)

Option 2 (slightly modified but rounds correctly):

str(round(@v, 1), 18, 1)

Option 3 (double conversion and rounding):

convert(varchar(20), convert(decimal(18,1), round(@v, 1)))

Option 4 (only double conversion):

convert(varchar(20), convert(decimal(18,1), @v))

Results: Option 1 and 2 are roughly 2 times slower than the last two but the result is right-justified. The fastest is Option 4.

Answer 1

The parameter to str() is a float so your decimal value is implicitly converted to a float(53) that is then converted to a string. So you see a floating point rounding error.

Do a slight modification to your query and you can see what is happening in the actual execution plan.

declare @v decimal(18,2) = 29.95
select top(1) str(@v, 18, 1)

<ScalarOperator ScalarString="str(CONVERT_IMPLICIT(float(53),[@v],0),(18),(1))">