SQL Server – Impact of has_unchecked_assembly_data After Changing Compatibility Level

The problem seems to be related to the fact that [TestGeocode].[ToString]() returns a max datatype (nvarchar(max)).

I also encounter the issue with this simpler version (changing the definition of c1 to varchar(8000) or using COALESCE instead of ISNULL resolves it)

DROP TABLE dbo.Test

CREATE TABLE dbo.Test
  (
     c1        VARCHAR(
                          MAX    --Fails
                        --  8000 --Works fine
                          ) NULL,
     comp1 AS CAST(ISNULL(c1, 'ABC') AS VARCHAR(100))
    CONSTRAINT UK_Test_comp1 UNIQUE NONCLUSTERED(comp1)
  )

GO

DECLARE @comp1 VARCHAR(100)

SELECT comp1
FROM   dbo.Test WITH (FORCESEEK)
WHERE  comp1 = @comp1 
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8606); 

Computed column references get expanded out to the underlying definition then matched back to the column later. This allows computed columns to be matched without referencing them by name at all and also allows simplification to operate on the underlying definitions.

ISNULL returns the datatype of the first parameter (VARCHAR(MAX) in my example). The return type of COALESCE will be VARCHAR(MAX) here too but it seems to be evaluated differently in a way that avoids the problem.

In the cases where the query succeeds the trace flag output includes the following

ScaOp_Convert varchar(max) collate 49160,Null,Var,Trim,ML=65535

    ScaOp_Const TI(varchar collate 49160,Var,Trim,ML=3) 
                      XVAR(varchar,Owned,Value=Len,Data = (3,ABC))

Where it fails this is replaced by

ScaOp_Identifier COL: ConstExpr1003 

I speculate that in the cases where it fails the (implicit) CAST('ABC' AS VARCHAR(MAX)) is just done once and this is evaluated as a runtime constant (more information). However the reference to this runtime constant label, instead of the actual string literal value itself, prevents it from matching the computed column definition.

This rewrite avoids the issue in your query

CREATE TABLE [dbo].[Test]
  (
     [test]        [VARCHAR](100) NULL,
     [TestGeocode] [geography] NULL,
     [Hashkey] AS CAST(
                        ( hashbytes
                            ('SHA', 
                                ( RIGHT(SPACE(100) + isnull([test], ''), 100) ) 
                                + RIGHT(SPACE(100) + isnull(CAST(RIGHT([TestGeocode].[ToString](),100) AS VARCHAR(100)), ''),100)
                            ) 
                        ) AS BINARY(20)                                                                                                        
                      ) PERSISTED
    CONSTRAINT [UK_Test_HashKey] UNIQUE NONCLUSTERED([Hashkey])
  )

However, execution plans still show an implicit conversion warning even though the conversion happens when the table is updated, not when it is read from.

Persisting a computed column does not guarantee that the persisted value will be used. The optimizer makes a cost-based decision between using the persisted value and computing the expression afresh, though there are also other factors in play. Simplifying, the process looks like this:

1. The computed column reference is always expanded to its definition before query compilation and optimization begins. This is very similar to the way view references are handled (unless indexed and referenced with a NOEXPAND hint). The expansion provides the greatest opportunity for orthogonal simplifications and optimizations to be applied.
2. Depending on the query and the code path taken through the query optimizer, a cardinality estimate may be requested for an expression in the expanded computed column definition. Deriving this cardinality estimation may result in a plan-affecting convert warning being added to an internal warning list.
3. The expanded computed column expression may be matched back to a persisted column or index later in the compilation and optimization process. Any plan-affecting convert warning added previously is not tracked and removed when this substitution occurs.

Like most warnings, the plan-affecting convert warning is opportunistic and informational. It is opportunistic in that it is only added if the optimizer follows a code path that attempts to compute cardinality on a suitable expression. This is similar to 'missing index suggestions', which are only added if the optimizer attempts to match to an 'ideal' index definition that is not found. To put it another way: neither of these facilities are based on exhaustive analysis.

I tried using a UDF in the computed column definition to get rid of the warning

This 'works' because the optimizer cannot expand a scalar UDF into its definition before optimization. The UDF is a 'black box' with guessed cardinality and horrible runtime performance (close to the cost of running a complete separate query per function invocation). Without expansion, a cardinality estimation that might generate the warning cannot occur.

Is there a reason to consider the warning anything other than a bug?

The warning indicates that the convert may affect plan quality because a cardinality estimation was performed on a problematic expression. The complexity of compilation and optimization is such that it is impossible to say if the cardinality estimate will affect the final quality of the plan or not, even if the computed column does end up being resolved to a persisted value or index.

So, the warning is generally useful because it indicates that internal optimization decisions may have been adversely impacted by the convert. I would always check a plan with this warning for inaccurate cardinality estimations, and any resulting performance or resource usage implications.

A final aside: the SQL Server version is not mentioned in the question, but for 2012 and later, TRY_CAST or TRY_CONVERT are more robust ways of handling this sort of requirement. This facility will not generally affect convert warnings one way or the other though.