By Mikael Eriksson


2013-01-25 08:07:59 8 Comments

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?

1 comments

@Mikael Eriksson 2013-02-07 16:39:38

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.

@John Eisbrener 2019-03-05 17:17:51

I would append the following to the last sentence, or the query is a view definition and queries will filter on the column showing the warning, in question.

Related Questions