Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
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?

share|improve this question
    
In SQL 2008 R2 it doesn't happen, the plan is free of warnings. So maybe it's something related only to 2012? –  Marian Jan 25 '13 at 8:59
    
I don't see a warning on build Microsoft SQL Server 2012 - 11.0.2218.0 (X64) –  Martin Smith Jan 25 '13 at 10:50
    
@MartinSmith It's in SQL Fiddle version Microsoft SQL Server 2012 - 11.0.2100.60 (X64) and I have it locally in Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) –  Mikael Eriksson Jan 25 '13 at 10:55
    
@MartinSmith It's not there if you only have one row in the table. You got to have zero rows or more than one. –  Mikael Eriksson Jan 25 '13 at 10:59
1  
@JonSeigel - It is a new warning for 2012. Don't know why I don't see it at all and for Mikael it is dependent on number of rows though. –  Martin Smith Jan 25 '13 at 18:25

1 Answer 1

up vote 1 down vote accepted

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.

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.