Why ROUND() function adding too many trailing zeros

create table #t(col varchar(10))
insert into #t values(1042800)

select col, ROUND(col,2) as RoundVal from #t

update #t set col = ROUND(col,2)

The select col, ROUND(col,2) from #t returns - as expected:

But update #t set col = ROUND(col,2) throws the following error:

Arithmetic overflow error for type varchar, value = 1042800.000000.

Question: I understand the error: I'm trying to convert a number into a VARCHAR variable, and the specified length of the VARCHAR variable is too short to accommodate the value. But why the update statement is adding 6 trailing zeros whereas select statement is (as expected) not adding any trailing zeros.

The ROUND function expects an "exact numeric or approximate numeric data type category" argument. When you use the ROUND function on the col column (which is a varchar(10)), it gets converted to a float data type, which does not have a fixed number of decimals (like a numeric or decimal data type).

When you try to convert the value 1042800 from a float to a varchar, it will implicitly try to use the scientific notation (because the number has more than 6 digits), which produces the result 1.0428e+006 and that will fit in a varchar(11).

See also https://dba.stackexchange.com/a/294936/29446 for an answer to a similar question.

To get the results you want, you could use:

update #t set col = CONVERT(NUMERIC(10,2),col)

This will explicitly specify a data type with 2 decimal digits, avoiding the use of scientific notation.