Type conversion may affect CardinalityEstimate

  • mceventphoto

    Ten Centuries

    Points: 1279

    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

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    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: 1279

    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: 109763

    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.

  • drew.allen

    SSC Guru

    Points: 76737

    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: 109763

    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.

  • mceventphoto

    Ten Centuries

    Points: 1279

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

    Thanks,

    MC

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

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