http://www.sqlservercentral.com/blogs/dave_ballantynes_blog/2011/11/06/execution-plan-warnings_1320_all-that-glitters-is-not-gold/

Printed 2014/04/21 08:55AM

Execution plan warnings–All that glitters is not gold

By Dave Ballantyne, 2011/11/06

In a previous post, I showed you the new execution plan warnings related to implicit and explicit warnings.  Pretty much as soon as i hit ’post’,  I noticed something rather odd happening.

This statement :

select top(10) 
SalesOrderHeader.SalesOrderID,
SalesOrderNumber
from Sales.SalesOrderHeader
join Sales.SalesOrderDetail
on SalesOrderHeader.SalesOrderID
= SalesOrderDetail.SalesOrderID
 
Throws the “Type conversion may affect cardinality estimation” warning.
 
image
 
Ive done no such conversion in my statement why would that be ?  Well, SalesOrderNumber is a computed column , “(isnull(N'SO'+CONVERT([nvarchar](23),[SalesOrderID],0),N'*** ERROR ***'))”,  so thats where the conversion is.
 
Wait!!! Am i saying that every type conversion will throw the warning ?  Thankfully, no.  It only appears for columns that are used in predicates ,even if the predicate / join condition is fine ,  and the column is indexed ( and/or , presumably has statistics). 
 
Hopefully , this wont lead to to many wild goose chases, but is definitely something to bear in mind.  If you want to see this fixed then upvote my connect item here.

Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.