Type conversion may affect CardinalityEstimate

Question

Type conversion may affect CardinalityEstimate

mceventphoto

Ten Centuries

Points: 1299
More actions
March 29, 2016 at 2:08 pm

#312362

The query below ran fine but I am trying to correct the "type conversion in expression may affect Cardinality Estimate in query plan" warning displayed in the query plan.
declare
@DY1 int = 20160329,
@DY2 int = 20160330,
@DY3 int = 20160331,
@DY4 int = 20160401,
@DY5 int = 20160402,
@DY6 int = 20160404
declare @rpt_AcrossDays table(
[AcrossDays] [int] NULL,
[DayName] [nvarchar](3) NULL,
[DayNo] [nvarchar](4) NULL
)
delete @rpt_AcrossDays
Insert into @rpt_AcrossDays(AcrossDays,[DayName],DayNo)
SELECT @DY1 AcrossDays,left(datename(dw,CONVERT(date, convert(char(10),@DY1, 120))),3) [DayName], 'DY1' DayNo union all
SELECT @DY2 AcrossDays,left(datename(dw,CONVERT(date, convert(char(10),@DY2, 120))),3) [DayName], 'DY2' DayNo union all
SELECT @DY3 AcrossDays,left(datename(dw,CONVERT(date, convert(char(10),@DY3, 120))),3) [DayName], 'DY3' DayNo union all
SELECT @DY4 AcrossDays,left(datename(dw,CONVERT(date, convert(char(10),@DY4, 120))),3) [DayName], 'DY4' DayNo union all
SELECT @DY5 AcrossDays,left(datename(dw,CONVERT(date, convert(char(10),@DY5, 120))),3) [DayName], 'DY5' DayNo union all
SELECT @DY6 AcrossDays,left(datename(dw,CONVERT(date, convert(char(10),@DY6, 120))),3) [DayName], 'DY6' DayNo
select * from @rpt_AcrossDays
Thanks,
MC

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 1

The key words in that message are "warning" (not "error") and "may" (not "will").

Every plan that uses type conversions, either implicit or explicit, will show that warning. Check where the conversion is, determine whether the warning applies in your case, and then move on.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

mceventphoto Ten Centuries Points: 1299 More actions · Answer 2

Thanks Hugo. In my case, I am moving on because it does not affect the result of my query. I was just trying to clean things up.

Thanks,

MC.

Sergiy SSC Guru Points: 110206 More actions · Answer 3

You're inserting varchar values 'DY1' DayNo into nvarchar(4) column.

It causes an implicit conversion and consequently, the warning.

If you either change the datatype to varchar(4) or use N'DY1' DayNo instead, it will all go away.

_____________
Code for TallyGenerator

drew.allen SSC Guru Points: 76944 More actions · Answer 4

Sergiy (3/29/2016)
You're inserting varchar values 'DY1' DayNo into nvarchar(4) column.
It causes an implicit conversion and consequently, the warning.
If you either change the datatype to varchar(4) or use N'DY1' DayNo instead, it will all go away.

He'll still have the explicit conversion of numeric to char to date, so I doubt that it will go away.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Sergiy SSC Guru Points: 110206 More actions · Answer 5

drew.allen (3/30/2016)
Sergiy (3/29/2016)
You're inserting varchar values 'DY1' DayNo into nvarchar(4) column.
It causes an implicit conversion and consequently, the warning.
If you either change the datatype to varchar(4) or use N'DY1' DayNo instead, it will all go away.
He'll still have the explicit conversion of numeric to char to date, so I doubt that it will go away.
Drew

Nah, those conversions are mentioned in "Compute Scalar" step.

They are executed when the recordset is prepared, not inserted.

_____________
Code for TallyGenerator

mceventphoto Ten Centuries Points: 1299 More actions · Answer 6

Thanks all for the suggestions. I used N'DY1' DayNo and the warning went away.

Thanks,

MC