sql-server add tag
dannnno (imported from SE)
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.

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][1])

    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;


  [1]: https://www.brentozar.com/pastetheplan/?id=H1CjyZvSS
Top Answer
Paul White (imported from SE)
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][1] 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.

  [1]: https://en.wikipedia.org/wiki/Monotonic_function

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.