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.