I have a problem with this query:
SELECT *
FROM Sales.SalesOrderDetail sod
JOIN Sales.SalesOrderHeader soh
ON sod.SalesOrderID = soh.SalesOrderID--- adventureworks db
Warnings
Type conversion in expression (CONVERT(nvarchar(23),[soh].[SalesOrderID],0)) may affect "CardinalityEstimate" in query plan choice
There's nothing to fix, really.
The message refers to this computed column in the Sales.SalesOrderHeader table:
[SalesOrderNumber] AS (isnull(N'SO'+CONVERT([nvarchar](23),[SalesOrderID]),N'*** ERROR ***')),
The warning is informational in this case, to be sure you're aware in case it was a problem, as discussed in this Q&A: Type conversion in expression may affect “CardinalityEstimate” - on a computed column?
IMO, that warning is nonsense making the warnings more like crying wolf.
In the cases where it can be hugely plan affecting (like rendering a seek through an index impossible because the convert on the index side, blogged about a long time ago here), we would like to see that warning. But when you just return the data, who cares?
Unfortunately, the same goes for the XE plan_affecting_convert event, making it also useless, IMO.
Type conversion in expression (CONVERT(nvarchar(23),[soh].[SalesOrderID],0)) may affect "CardinalityEstimate" in query plan choice
Since you are not aware about this warning message.It is important to point other example where this warning message MATTER.
When there is data type mismatch between 2 Join condition then also you get this error. In such case it greatly impact the performance.
So you make the data type similar.
CREATE TABLE myTable (ID INT, Col VARCHAR(100))
GO
INSERT INTO myTable (ID, Col)
SELECT 1, 'X'
UNION ALL
SELECT 2, 'Y'
UNION ALL
SELECT 3, 'Z'
GO
GO
--Check the execution Plan
SELECT *
FROM myTable
WHERE ID = N'1' AND Col = N'X'
GO
Here N'1' (NVarchar) is converted to int.
NVarchar precedence is lower then INT. Data type of lower precedance is converted to data type of higher precedence.
So if there afe millions of rows involve and you are getting same Warning ,that means CONVERT will happen for each rows in resultset. It will affect Cardianility estimate.
