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.
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:
- 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?
- What cardinality estimate is actually at risk here, and what operation would change based off of inaccuracies in that cardinality estimate?
- 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?
- 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;