Execution plan warnings–All that glitters is not gold

6 November 2011 · Originally published on sqlblogcasts.com (“SQL and the like”)

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.

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.