How to concatenate two column of different types in sql? (without getting some strange warning)

Question

When I try to concatenate two columns, of different type, I get the following warning:

Type conversion in the expression (CONVERT_IMPLICIT(varchar(41), [TABLE].[COLUMN], 0)) can affect "CardinalityEstimate" in choosing query plan.

By doing a search... I seem to have understood that this warning can derive from the comparison of two columns of different types. For this reason I wanted to carry out some simple tests to deepen the thing:

CREATE TABLE TABLE_A (
    ID DECIMAL(5, 0) NOT NULL,
    COLUMN_A VARCHAR(255) NULL
)

INSERT TABLE_A (ID, COLUMN_A) VALUES (1, 'TEST_1')
INSERT TABLE_A (ID, COLUMN_A) VALUES (2, 'TEST_2')
INSERT TABLE_A (ID, COLUMN_A) VALUES (3, 'TEST_3')

Trying to concatenate with the operator, two columns of different types, an error is returned: "Error converting data type from varchar to numeric."

SELECT ID + COLUMN_A
FROM TABLE_A

If I try to do the same thing but converting the "ID" column, I no longer have the error but I get the previously mentioned warning.

SELECT CAST(ID AS VARCHAR(255)) + COLUMN_A
FROM TABLE_A

I have the same result if I try to concatenate using the concat() function, no error but I have the warning.

SELECT CONCAT(ID, COLUMN_A)
FROM TABLE_A

My question is this, how is it possible that I get a warning about comparing two columns even when they should be equal when I cast? How can I concatenate two columns of different types without that warning being returned?

Thanks in advance, Giacomo.

Answer 1

The 'warning' is purely informational. The compilation process produces them automatically, there's nothing you can do to turn them off. It produces them even when they're not useful.

If your query performed slowly, or you noticed that cardinality estimates in the execution plan were incorrect, the 'warning' would give you information about where to look for a possible cause.

If you don't have those concerns, you can safely ignore the 'warning'.

---

Just because I know someone will answer your question literally, yes, it is possible to concatenate without the warning, but there's no need. This is one of the slower ways to do it, but does avoid the warning:

-- Don't use this
SELECT FORMAT(TA.ID, 'N0') + TA.COLUMN_A
FROM dbo.TABLE_A AS TA;