TRY_CAST in the WHERE clause.

  • Comments posted to this topic are about the item TRY_CAST in the WHERE clause.

  • Nice, easy one to start the week on, thanks Steve

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

  • Should the SELECT have a try_cast in it as well?
    My understanding is that with larger data sets, it is possible for the optimizer to validate results before applying filters.
    I used to see it happen occasionally with division in SELECT statements and a WHERE clause that trapped for zero in the denominator column.

  • Perhaps. Didn't add one as it wasn't on my mind here.

  • Any time you're doing a CAST and there is uncastable data in a column, you should use TRY_CAST.  If TRY_CAST isn't available, the CAST should be wrapped in a CASE statement with a well crafted WHEN clause to guard the CAST.  The optimizer is free to perform calculations in SELECT clauses before filtering.  I have seen this issue cause problems in multiple production systems, sometimes after years without any issues.

Viewing 5 posts - 1 through 4 (of 4 total)

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