Inserting with implicit type conversion causes warning for cardinality estimates

Question

I noticed this while doing some performance testing recently. When I insert a value into a column that will require an implicit conversion (e.g. bigint into nvarchar), I get a warning:

Type conversion in expression (CONVERT_IMPLICIT(nvarchar(50),[tempdb].[dbo].[#MyFunIntTable].[EvenCoolerColumn],0)) may affect "Cardinality Estimate" in query plan choice.

Being a concerned citizen, I checked all of the obvious suspects and eventually dug into the XML to confirm that it was actually warning about the insert into the table. The problem is, I can't figure out why this would ever affect cardinality estimates. If I were doing this in a join or somewhere with a little more logic it would make sense, but there shouldn't be a cardinality estimate mismatch for the actual insert operation, right?

I noticed that this happened when it was more than just a trivial query - as soon as more than one value is inserted, or we're pulling a value from a table, we hit this.

This question has attracted some potential duplicates, including:

• Why am I getting an implicit conversion of Int / Smallint to Varchar, and is it really impacting Cardinality Estimates?
• Type conversion in expression may affect "CardinalityEstimate" in query plan choice?

I think it is different from these questions because I'm literally not doing anything with this column. I'm not using it in a filter, or a sort, or a grouping, or a join, or in a function - any of these things makes the scenario more complicated. All I'm doing is inserting a bigint into a nvarchar, which should never impact a meaningful cardinality estimate that I can think of.

What I'm specifically looking for out of an answer is:

1. An explanation of why I get this warning despite nothing of interest going on - is it just that SQL Server will be conservative and report even when it won't affect plan choice?
2. What cardinality estimate is actually at risk here, and what operation would change based off of inaccuracies in that cardinality estimate?
3. Is there a scenario where this could affect plan choice? Obviously if I start joining or filtering on the converted column it could, but as-is?
4. Is there anything that can be done to prevent it from warning, besides changing data types (assume this is a requirement of how the data models interact)

I recreated it with the below simple example (paste the plan)

DROP TABLE IF EXISTS #MyFunStringTable;
DROP TABLE IF EXISTS #MyFunIntTable;

CREATE TABLE #MyFunStringTable
(
  SuperCoolColumn nvarchar(50) COLLATE DATABASE_DEFAULT NULL
);

CREATE TABLE #MyFunIntTable
(
  EvenCoolerColumn bigint NULL
);

INSERT INTO #MyFunIntTable
( EvenCoolerColumn )
VALUES
( 1 ),
( 2 ),
( 3 ),
( 4 ),
( 5 );

INSERT INTO #MyFunStringTable
( SuperCoolColumn )
  SELECT EvenCoolerColumn
    FROM #MyFunIntTable;

INSERT INTO #MyFunStringTable
( SuperCoolColumn )
VALUES
( 1 );

INSERT INTO #MyFunStringTable
( SuperCoolColumn )
VALUES
( 1 ),
( 2 );

INSERT INTO #MyFunStringTable
( SuperCoolColumn )
  SELECT 1;

INSERT INTO #MyFunStringTable
( SuperCoolColumn )
SELECT 1
UNION ALL
SELECT 2;

INSERT INTO #MyFunStringTable
( SuperCoolColumn )
  SELECT 1
    FROM #MyFunIntTable;

Answer 1

Like other execution plan warnings, this one is informational. If your query performed slowly, or you noticed that cardinality estimates were incorrect, the warning would give you information about where to look for a possible cause.

As a purely practical matter, that is pretty much the end of it. The precise conditions that trigger this optimizer warning are undocumented.

That said, I will go into a little detail to satisfy a little of your curiosity.

1. An explanation of why I get this warning despite nothing of interest going on - is it just that SQL Server will be conservative and report even when it won't affect plan choice?

There are things going on of interest to the cardinality estimation component of the query optimizer. This component keeps track of a wide range of properties and other information for each logical operator in the query tree. This includes histograms (from disk, or derived entirely in-memory), domain information, functional dependencies etc.

In your specific example, the warning is triggered when the input to the convert is known to be monotone but the output is not. Conversion from integer literal to bigint retains this property. Conversion from bigint to nvarchar(50) does not.

When there is a single literal integer constant, parse-time constant-folding converts this to nvarchar before optimization starts. This avoids type conversion in the plan, and the associated warning.

There are detailed internal implementation differences between the original cardinality estimator and the new cardinality estimator that mean some statements will generate a warning under one CE but not the other.

2. What cardinality estimate is actually at risk here, and what operation would change based off of inaccuracies in that cardinality estimate?

In your example statements, none. As noted, the conversion can affect other internal data, which might affect e.g. other plan operators, or the code path taken through the optimizer. The broad point is that there is more than a raw cardinality estimate to consider. Internal differences can affect things like indexed view or computed column matching, among other possibilities.

3. Is there a scenario where this could affect plan choice? Obviously if I start joining or filtering on the converted column it could, but as-is?

This appears to be the same as the previous question.

4. Is there anything that can be done to prevent it from warning, besides changing data types (assume this is a requirement of how the data models interact)

There is no option to turn off these warnings.