Sql-server – Type conversion in expression may affect “CardinalityEstimate” – on a computed column

cardinality-estimatescomputed-columnoptimizationsql serversql-server-2016

I had a look at this question:

Type conversion in expression may affect “CardinalityEstimate” in query plan choice?

But that was related to collation and not with computed columns.

I have the following table definitions that are used in a query below that is giving me a query hint

Type conversion in expression
(CONVERT(varchar(10),[t].[FLTCD_FLT_DATE],112)) may affect
"CardinalityEstimate" in query plan choice

enter image description here

Please note the computed column flightReference

CREATE TABLE [dbo].[repl_Transportation] ( 
[FLIGHT#]          INT                              NOT NULL,
[FLTCD_FLT_DATE]   DATETIME                             NULL,
[FLTCD_DEP_GATE]   CHAR(3)                              NULL,
[FLTCD_ARR_GATE]   CHAR(3)                              NULL,
[FLTCD_SEQUENCE]   CHAR(1)                              NULL,
[DIRECTION]        CHAR(1)                              NULL,
[PNR_NUMBERS]      VARCHAR(70)                          NULL,
[HK]               NUMERIC(3,0)                         NULL,
[Create_Date]      DATETIME                         NOT NULL,
[Modify_Date]      DATETIME                             NULL,
[flightReference]  AS (substring(((CONVERT([varchar](10),[FLTCD_FLT_DATE],
 (112))+[FLTCD_DEP_GATE])+[FLTCD_ARR_GATE])+[FLTCD_SEQUENCE],(3),(13))) PERSISTED,
CONSTRAINT   [PK_FLIGHT#]  PRIMARY KEY CLUSTERED    ([FLIGHT#] asc))



IF OBJECT_ID('[dbo].[repl_Transportation_Details]') IS NOT NULL 
DROP TABLE [dbo].[repl_Transportation_Details] 
GO
CREATE TABLE [dbo].[repl_Transportation_Details] ( 
[FLT_LEG_ID]     INT                              NOT NULL,
[FLIGHT#]        INT                              NOT NULL,
[LEG_NO]         TINYINT                              NULL,
[AIRLINE_CODE]   CHAR(2)                              NULL,
[AIRLINE_FLTNO]  VARCHAR(6)                           NULL,
[DEP_DATE_TIME]  DATETIME                             NULL,
[ARR_DATE_TIME]  DATETIME                             NULL,
[DEP_AIRPORT]    CHAR(3)                              NULL,
[ARR_AIRPORT]    CHAR(3)                              NULL,
[Create_Date]    DATETIME                             NULL,
[Modify_Date]    DATETIME                             NULL,
CONSTRAINT   [PK_FLT_LEG_ID]  PRIMARY KEY CLUSTERED    ([FLT_LEG_ID] asc))

When I run the following query I get a warning on the select:
The query plan is here

Type conversion in expression
(CONVERT(varchar(10),[t].[FLTCD_FLT_DATE],112)) may affect
"CardinalityEstimate" in query plan choice

    SELECT  t.FLIGHT#,
    t.flightReference,
    PNR_NUMBERS,
    MIN(td.DEP_DATE_TIME)     AS departDate,
    MAX(td.ARR_DATE_TIME)     AS arrivalDate,
    CASE WHEN 
     PNR_NUMBERS IS NULL OR PNR_NUMBERS = '' 
     THEN 'Paper Ticket' 
     ELSE 'E-Ticket' 
    END          AS ticketType,
    'US'         AS source,
    0          as sourceId,
    DIRECTION,
    FLTCD_DEP_GATE,
    FLTCD_ARR_GATE
 FROM        repl_Transportation      AS t 
 LEFT JOIN repl_Transportation_Details    AS td ON td.FLIGHT# = t.FLIGHT#
 GROUP BY t.FLIGHT#, 
    t.flightReference, 
    PNR_NUMBERS,
    DIRECTION,
    FLTCD_DEP_GATE,
    FLTCD_ARR_GATE

When I remove the MIN and MAX columns from the select,
there is no more any warnings, the query plan is here

    SELECT  t.FLIGHT#,
    t.flightReference,
    PNR_NUMBERS,
    --MIN(td.DEP_DATE_TIME)     AS departDate,
    --MAX(td.ARR_DATE_TIME)     AS arrivalDate,
    CASE WHEN 
     PNR_NUMBERS IS NULL OR PNR_NUMBERS = '' 
     THEN 'Paper Ticket' 
     ELSE 'E-Ticket' 
    END          AS ticketType,
    'US'         AS source,
    0          as sourceId,
    DIRECTION,
    FLTCD_DEP_GATE,
    FLTCD_ARR_GATE
 FROM        repl_Transportation      AS t 
 LEFT JOIN repl_Transportation_Details    AS td ON td.FLIGHT# = t.FLIGHT#
 GROUP BY t.FLIGHT#, 
    t.flightReference, 
    PNR_NUMBERS,
    DIRECTION,
    FLTCD_DEP_GATE,
    FLTCD_ARR_GATE

enter image description here

Is there any way I can get rid of this warning?

Is it a persisted column, the conversion has been already done, why exactly is the query optimiser going on about it?

Best Answer

I had a look at this question:

Type conversion in expression may affect “CardinalityEstimate” in query plan choice?

But that was related to collation and not with computed columns.

It's the same issue. The warning is informational, to help you understand any issues you might notice with cardinality estimation.

Is there any way I can get rid of this warning?

Is it a persisted column, the conversion has been already done, why exactly is the query optimiser going on about it?

Just because the computed column is persisted, does not mean the optimizer will choose to use the persisted value. SQL Server routinely expands computed columns (persisted or not) into the underlying definition at the start of the compilation process. Later on, it may, or may not, match expressions back to the available persisted computed columns. When the optimizer encounters the expression, it may add a warning to the plan.

The only way to prevent this expansion and match-back is to enable trace flag 176 on suitable versions.

When I remove the MIN and MAX columns from the select, there is no more any warnings

With this change, the optimizer is able to simplify the query to remove the unnecessary join. The result is a TRIVIAL plan, with no cost-based choices, so there is no warning about plan choices.

Related Question