SELECT vs UPDATE, Unexpected rounding when using ABS function

Question

Attached is a code sample to run in SQL. This seems like unexpected behavior for SQL Server. What should happen is to remove the negative from the number but when using the same function under the update command it does the absolute value and also rounds the number. Why is this?

DECLARE @TEST TABLE (TEST varchar(2048));

INSERT INTO @TEST VALUES ('  -29972.95');

SELECT TEST FROM @TEST;

SELECT ABS(TEST) FROM @TEST;

UPDATE @TEST SET TEST = ABS(TEST);

SELECT TEST FROM @TEST;

Below are the results of that code.

  -29972.95
29972.95
29973

Answer 1

This seems more a "feature" of the CONVERT function than anything to do with SELECT or UPDATE (only reason it is different is because the UPDATE implicitly converts the FLOAT(8) returned by ABS(...) back into VARCHAR).

The compute scalar in the update plan contains the expression

[Expr1003] = Scalar Operator(CONVERT_IMPLICIT(varchar(2048),
                                              abs(CONVERT_IMPLICIT(float(53),[TEST],0))
                                              ,0) /*<-- style used for convert from float*/
                            )

Value - Output

0 (default) - A maximum of 6 digits. Use in scientific notation, when appropriate.

1 - Always 8 digits. Always use in scientific notation.

2 - Always 16 digits. Always use in scientific notation.

From MSDN: https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017

This can be seen in the example below:

SELECT
    [# Digits],
    CONVERT(FLOAT(8), CONVERT(VARCHAR(20), N)) AS [FLOAT(VARCHAR(N))],
    CONVERT(FLOAT(8), CONVERT(VARCHAR(20), N, 0)) AS [FLOAT(VARCHAR(N, 0))],
    CONVERT(FLOAT(8), CONVERT(VARCHAR(20), N, 1)) AS [FLOAT(VARCHAR(N, 1))]
FROM (SELECT '6 digits', ABS('9972.95') UNION ALL SELECT '7 digits', ABS('29972.95')) T ([# Digits], N)

This returns the following results:

# Digits FLOAT(VARCHAR(N)) FLOAT(VARCHAR(N, 0)) FLOAT(VARCHAR(N, 1))
-------- ----------------- -------------------- --------------------
6 digits 9972.95           9972.95              9972.95
7 digits 29973             29973                29972.95

This proves the UPDATE was using CONVERT(VARCHAR, ABS(...)) effectively with the default style of "0". This limited the FLOAT from the ABS to 6 digits. Taking 1 character away so it does not overflow the implicit conversion, you retain the actual values in this scenario.

Taking this back to the OP:

• The ABS function in this case is returning a FLOAT(8) in the example.
• The UPDATE then caused an implicit conversion that was effectively `CONVERT(VARCHAR(2048), ABS(...), 0), which then overflowed the max digits of the default style.
• To get around this behavior (if this is related to a practical issue), you need to specify the style of 1 or 2 (or even 3 to get 17 digits) to avoid this truncation (but be sure to handle the scientific notation used since it is now always returned in this case)

Answer 2

(some preliminary testing deleted for brevity)

It definitely has to do with silent truncating during INSERT/UPDATEs.

If you change the value insertion to this:

INSERT INTO @TEST SELECT ABS('  -29972.95')

You immediately get the same rounding/truncation without doing an UPDATE.

Meanwhile, SELECT ABS(' -29972.95') produces expected results.

Further testing supports the theory of an implicit float conversion, and indicates that the culprit lies with the conversion back to varchar:

DECLARE @Flt float = '  -29972.95'

SELECT @Flt;

SELECT CAST(@Flt AS varchar(2048))

Produces:

-29972.95

-29972

Probably final edit:

I was sniffing up the same tree as Martin. I found this.

Which made me try this:

DECLARE @Flt float = '  -29972.95'

SELECT @Flt;

SELECT CONVERT(varchar(2048),@Flt,128)

Which produced this:

-29972.95

-29972.95

So I'm gonna call this kinda documented since the 128 style is a legacy style that is deprecated and may go away in a future release. But none of the currently documented styles produce the same result. Very interesting.