create table T(ID int identity primary key)
insert into T default values
insert into T default values
go
select cast(ID as varchar(10)) as ID
from T
where ID = 1
The query above has a warning in the query plan.
<Warnings>
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT(varchar(10),[xx].[dbo].[T].[ID],0)" />
</Warnings>
Why does it have the warning?
How could a cast in the field list affect the cardinality estimate?
This warning was new for SQL Server 2012.
From New "Type Conversion in Expression....." warning in SQL2012 ,to noisy to practical use
I see what you mean. While I agree that this is noise in most cases, it is low priority for us to fix. We will look at it if we get more feedback. For now I have closed this by design.
Connect was killed and it doesn’t look like the original issue was transferred to UserVoice. Here’s a different UserVoice issue about the same problem, Type conversion in may affect CardinalityEstimate - Convert/cast on selected columns
I will provide the boring answer until someone comes along with a better one.
Why does it have the warning?
Speculation on my part.
There is a cast on a column that is used in the where clause which make statistics of that column interesting. A change of datatype makes the statistics no good so lets warn about that in case the value from the field list might end up to be used somewhere.
How could a cast in the field list affect the cardinality estimate?
It can't unless it is the field list in a derived table.