6

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.

0

1 Answer 1

14

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'.

example


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;

nope

1
  • Impeccable, clear and concise. I assumed it was purely informative but I had to confirm. Feb 14 at 14:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.