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