5

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.

  • Can you upload the execution plan (maybe to pastebin) and link to it in your question? – Max Vernon Dec 17 '15 at 22:20
  • 2
    If the warning is there in spite of the performance being excellent, do you really care that it is there? – Aaron Bertrand Dec 17 '15 at 22:24
  • @AaronBertrand: I see your point and I can live with that annoyance, but I'd prefer to not get a warning when there is no good reason for it. I completely fail to understand why using a UDF in the calculation should impact the execution plan of a SELECT. If it wasn't PERSISTED, sure. But for a PERSISTED computed column, it shouldn't treat it any differently than any other column. But clearly it does. – Mark Freeman Dec 18 '15 at 15:24
  • @MaxVernon: I've seen this with several computed column I implemented, some with very simple calculations (changing a CHAR 'Y' or 'N' to a BIT) and some more complex such as the one I posted. In all cases, they result in the same warning as the original version of the table and query, even though the PERSISTED computed column and revised index results in a truly optimal plan. I'll make a post on Connect, which I'm sure they will mark as won't fix. I do see why something like this would be a low priority. – Mark Freeman Dec 18 '15 at 15:31
  • @Mark - have you seen this? – Max Vernon Dec 18 '15 at 16:28
9

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.

3

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.

  • Solomon: Thanks for the suggestion. While a trigger will certainly work, I've heard that a trigger involves more overhead than a converted column. Is there any performance or scalability reason to use a persisted computed column rather than a trigger? – Mark Freeman Dec 18 '15 at 22:21
  • @Mark I haven't tested it so I am not making a definite statement on the relative difference in performance between the two options. I am saying that operationally it is the same thing, and it won't have the warning, and if you needed to do a filtered index or filtered statistic, it would work. Even if the performance is a little worse, how many inserts does this table get, and how often is that field updated? – Solomon Rutzky Dec 18 '15 at 22:28
2

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
  • Actually, no. While adding that explicit CONVERT is a good idea for clarity of intent (and we did go ahead and implement that this morning) it doesn't remove the warning. It shouldn't matter what the formula is, In a persisted computed column, that work is all done during the INSERT or UPDATE to the table, long before we SELECT from it. IMHO, SELECT shouldn't even take notice that it is a computed column, it should just accept the value and type like it would any other column. – Mark Freeman Dec 18 '15 at 15:20
  • 1
    If you actually include the explicit convert, it should/may remove the warning about the implicit conversion to int, which is potentially coming from the CASE statement, which does have an implicit convert in it. However, you will still see a warning about those CONVERT statements affecting cardinality estimates, which Paul explains so well in his answer. – Mister Magoo Dec 20 '15 at 0:52
0

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.

  • 1
    This doesn't look like an answer to your question. – Martin Smith Apr 27 '16 at 15:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.