SQL function str () vs round ()

Question

While working with the str () function, I found that in some cases it bypasses the error, and the round () function works as expected. See an 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 this is happening?

EDIT1 : I tested various 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 (original, in some cases erroneous):

 str(@v, 18, 1) 

Option 2 (slightly modified, but rounded 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 (double conversion only):

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

Results : Options 1 and 2 are about 2 times slower than the last two, but the result is justified. The fastest option is option 4.

Answer 1

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

Make a slight modification to your request and you will see what happens 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))"> 

Answer 2

Syntax STR (); STR (float_expression [, length [, decimal]]) clearly says that the number is float_expression. Therefore, no matter what number you give, it is first converted to FLOAT (n), where the default value is n = 53.

So,

SELECT STR (4.65,5,1), SELECT STR (3.65,5,1)

Equally:

SELECT STR (CAST (4.65 AS FLOAT (53)), 5.1), STR (CAST (3.65 AS FLOAT (53)), 5.1)

If you specify n, say n = 4, it will give the answer you expect (i.e. 4.7 and 3.7)

SELECT STR (CAST (4.65 AS FLOAT (4)), 5.1), STR (CAST (3.65 AS FLOAT (4)), 5.1) - 4.7, 3.7