Inserting with implicit type conversion causes warning for cardinality estimates
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.