Post reply

TRY_CAST in the WHERE clause.

  • May 12, 2018 at 2:54 pm

    #77346

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

  • May 14, 2018 at 3:16 am

    #1990321

    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”

  • May 14, 2018 at 7:27 am

    #1990342

    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.

  • May 15, 2018 at 4:04 am

    #1990478

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

  • May 15, 2018 at 10:33 am

    #1990550

    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