Implicit conversion warning despite persisted computed column vs. performance hit from UDF in persisted computed column definition

Question

I have a need for a computed column to resolve an implicit conversion problem. I have a column that is declared as a VARCHAR that should only ever store integers, but is occasionally populated in error by a third-party application with strings, so that column needs to stay as is.

The table is often joined to one that stores that value as an INT. I defined a PERSISTED computed column that converts that VARCHAR to an INT:

[iOrderNumber] AS (CONVERT([int],
case
   when [ordernumber] like '#%' then (-99) when isnumeric([ordernumber])=(0) then (-99) 
   when CONVERT([bigint],[ordernumber],(0))>(2147483647) then (-99)
   else [ordernumber]
end,(0))) PERSISTED NOT NULL

With an appropriate index on the computed column, performance dramatically improved for queries that could now join to the INT column. 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.

I tried using a UDF in the computed column definition to get rid of the warning (I found a blog post that suggested that), but then execution of my queries took much longer and used much more CPU although the logical reads stayed the same. But the UDF did eliminate the warning.

Is there a reason to consider the warning anything other than a bug? Is there a reason to consider the optimizer's handling of a persisted computed column defined with a UDF as anything other than a bug?

More importantly, is there any way to get rid of the warning without incurring the performance penalty from the UDF solution?

I considered using a trigger and a translation table containing the VARCHAR and INT versions of the data instead of using a computed column, but that seems like a lot of unnecessary overhead.

Answer 1

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.

Answer 2

Rather than mess with a Persisted Computed Column, or even a translation table, just add a regular 'ol INT field that is maintained via a Trigger. That is really not any different than what you are currently attempting since you are taking up the 4 bytes for the INT in both cases and performing a triggered calculation upon insert or update in both cases.

1. Add the field:

   ALTER TABLE [{table_name}]
     ADD [OrderNumberINT]
     NOT NULL
     CONSTRAINT [DF_{table_name}_OrderNumberINT]
     DEFAULT (-99);
   

2. Then create an AFTER INSERT, UPDATE Trigger that will populate the OrderNumberINT field.

3. Rather than doing the CASE statement and using ISNUMERIC which will return true for stings that will not convert to an INT or BIGINT, use the TRY_CONVERT function that was introduced in SQL Server 2012:

   IF (UPDATE([OrderNumberINT]))
   BEGIN
     UPDATE tbl
     SET    tbl.[OrderNumberINT] = ISNULL(TRY_CONVERT(INT, ins.[OrderNumber]), -99)
     FROM   [{table_name}] tbl
     INNER JOIN  INSERTED ins
             ON  ins.IDfield = tbl.IDfield;
     --WHERE  TRY_CONVERT(INT, ins.[OrderNumber]) IS NOT NULL
   END;
   

4. Do a one-time UPDATE of the table to do the initial population of the field, also using the TRY_CONVERT function:

   UPDATE tbl
   SET    tbl.[OrderNumberINT] = TRY_CONVERT(INT, tbl.[OrderNumber])
   FROM   [{table_name}] tbl;
   WHERE  TRY_CONVERT(INT, tbl.[OrderNumber]) IS NOT NULL
   

   I re-used the TRY_CONVERT in the SET clause rather than using CONVERT in the hopes of the Query Optimizer re-using the expression and not running it twice.

5. If the system is live while you are doing this work, do all of this within a BEGIN TRAN / COMMIT and take an exclusive lock on the table, and maybe set the TRANSACTION ISOLATION LEVEL to SERIALIZABLE.

Answer 3

It seems to me that the warning is coming from the structure of your CASE statement, where you mix types: INT with VARCHAR.

If you explicitly CAST your VARCHAR column as INT, you should remove the warning.

[iOrderNumber] AS (CONVERT([int],
case
   when [ordernumber] like '#%' then (-99) when isnumeric([ordernumber])=(0) then (-99) 
   when CONVERT([bigint],[ordernumber],(0))>(2147483647) then (-99)
   else CONVERT([int], [ordernumber], (0))
end,(0))) PERSISTED NOT NULL

Answer 4

Apparently, there is at least one related bug Microsoft has decided not to fix. Apparently, you also can't create a filtered index on a persisted computed column either, although SSDT thinks you can. Microsoft hasn't even commented on the latter issue in 4 years.