Using SQL Server 2016 I'm trying to understand why I receive this warning while running this T-SQL statement (I chose the table variable for ease of testing. This happens with DB tables that have columns of
int type as well):
declare @test as table( col1 int ) insert into @test (col1) values(30500600) select LEFT(test.col1, 2) from @test as test
30 is returned as expected, but upon examining the Execution Plan I see a warning for the
SELECT statement that reads:
Type conversion in expression (CONVERT_IMPLICIT(varchar(12),[test].[col1],0)) may affect "CardinalityEstimate" in query plan choice
If I run the same above without pulling from a table:
select LEFT(30500600, 2)
30 is returned as expected and there is no warning in the Execution Plan.
Both statements should be performing
int type so I don't understand why one returns a warning and the other doesn't. What's going on here?
I've tried changing the
col1 datatype declaration to
varchar and that clears up the error. But that still doesn't explain to me why
select LEFT(30500600, 2) by itself doesn't cause a warning if I'm passing an
int directly to the
varchar in the original query:
LEFT(cast(test.col1 as varchar(12)), 2) which returns the same warning.
The linked duplicate states that computed columns were the issue for that user. There are no computed columns in my example, so that isn't the issue. Which leads me to the second part of the answer regarding implicit conversion to string types using the
CONCAT function which is also the same for the
LEFT function. In the second paragraph of my edit above I used
CAST to explicitly convert to
varchar so I wouldn't think that a conversion would be taking place? I'm obviously missing something here.