Sql-server – What Triggers This Warning: Type Conversion in Expression May Affect “CardinalityEstimate” in Query Plan Choice

cardinality-estimatessql serversql-server-2016

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 LEFT() on int type so I don't understand why one returns a warning and the other doesn't. What's going on here?

Edit:

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 LEFT() function.

I've tried cast to varchar in the original query: LEFT(cast(test.col1 as varchar(12)), 2) which returns the same warning.

Possible Duplicate

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.

Best Answer

The reason there is no "type conversion / cardinality estimate" warning in this query:

SELECT LEFT(30500600, 2);

...is because there is no cardinality estimate. It's a constant expression being passed into a deterministic function - guaranteed to only return one "row." I think the explanation is as simple as that, since the warning is more about the potential cardinality problems (rather than just the simple existence of the type conversion).

If I get the estimated execution plan for that statement, it's just a single "select without query" element:

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.518" Build="13.0.5081.1" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementId="1" StatementText="select LEFT(30500600, 2)" StatementType="SELECT WITHOUT QUERY" RetrievedFromCache="false" />
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

For what it's worth, even the table variable version doesn't display the warning in SQL Server 2017 (because it's really a pointless warning when the conversion can't possibly affect a cardinality estimate - as in your example).

To be clear, that warning isn't always pointless. Take this example:

SELECT message_id 
INTO #SomeNumbers
from sys.messages;

SELECT * 
FROM #SomeNumbers
WHERE LEFT(message_id, 2) = '50';

This puts 280,192 rows into a temp table. The SELECT query has this warning, and the warning is legit. SQL Server estimates there are 28,019.2 matching rows (10% of the table), when in fact there are only 2,156 matching rows (<1% of the table).

Regarding your example where you cast this input into the LEFT function, notice that the warning has changed slightly from CONVERT_IMPLICIT to just CONVERT:

Type conversion in expression (CONVERT(varchar(12),[test].[col1],0)) may affect "CardinalityEstimate" in query plan choice

So you're just replacing an implicit conversion with an explicit one, which still warrants a warning.

Related Question