Loading ...
Sorry, an error occurred while loading the content.

RE: [tampasql] TSQL short circuiting - safe to rely on?

Expand Messages
  • Jeffrey Garbus
    I always test that out when I care; try the query, and see if you get any I/O. No I/O, short circuit worked. (SQL Server Usually gets it right) Have a great
    Message 1 of 6 , Feb 2, 2012
    • 0 Attachment

      I always test that out when I care; try the query, and see if you get any I/O. No I/O, short circuit worked.

       

      (SQL Server Usually gets it right)

       

      Have a great day!

       

      Jeff Garbus

       

      *** Check out my new blog, http://mssqlperformance.blogspot.com/ ***

      *** Check out my other new blog, http://aseperformance.blogspot.com/ ***

       

      My new book -- Microsoft Transact - SQL, The Definitive Guide -- Available NOW!

      Use coupon code "GARBUS" for a 35% discount when ordering

       

      813/966 1180 (mobile)

      813/641 3434 (office)

      813/354-4681 (fax)

      Jeffrey.Garbus (skype)

       

      From: tampasql@yahoogroups.com [mailto:tampasql@yahoogroups.com] On Behalf Of Jun Sato
      Sent: Tuesday, January 31, 2012 10:49 AM
      To: tampasql@yahoogroups.com
      Subject: [tampasql] TSQL short circuiting - safe to rely on?

       

       

      Hello all,

       

      Just wanted to get group opinion on something - is it safe to rely on short circuiting in IF's and CASE WHEN's?

      i.e. let's say I have something like:

       

      -- say yay if it has valid integer evaluating to 1234 (like 00001234)

      -- say nay if not.

       

      declare @myvar varchar(100)

      set @myvar = 'UNKNOWN' -- this should say nay

       

      select case when IsNumeric(@myvar) = 1 AND

                       Cast(@myvar as int) = 1234

             then 'yay' else 'nay' end

       

      The query above works, but what I'm thinking is...is TSQL allowed to evaluate conditions in any way it likes? i.e. will it ever do: 

       

      select case when Cast(@myvar as int) = 1234 AND

                       IsNumeric(@myvar) = 1

             then 'yay' else 'nay' end

       

      ..and break on type cast?

       

      As you know, in certain languages you can count on short circuiting. I can't seem to dig up official info on MS T-SQL - though I do know that ANSI SQL says it's implementation dependent so it's up to Microsoft as to how they go abuout it...my guess is that TSQL is really goot at short-circuiting for optimization reasons and it *usually* goes from left to right but not always, though I got nothing to verify that. Does anyone have definitive info on this?

       

      thanks and have a good day -

      Jun

       

       

       

    • Jonathan Kehayias
      Hey Jeff, The problem is the situations where SQL doesn t get it right, especially for builtin functions like ISNUMERIC() as Mike pointed out and more complex
      Message 2 of 6 , Feb 2, 2012
      • 0 Attachment
        Hey Jeff,

        The problem is the situations where SQL doesn't get it right, especially for builtin functions like ISNUMERIC() as Mike pointed out and more complex for ISDATE() which is commonly a point of failure I see in client environments that make use of a EAV (entity attribute value) table for customize fields or storing variant datatypes as strings instead of using a sql_variant datatype.  Short circuiting is not guaranteed by SQL Server for these scenarios so it should never be relied upon because it could result in a breaking change from version to version.

        I am not saying that I haven't written code in the past that was formulated around this working.  I can actually think of two specific cases where I did write code that expected this to work out, and later found out that it didn't when something changed in the data set.

        Jonathan Kehayias, MCITP
        jmkehayias@...
        SQL Server MVP
        http://sqlblog.com/blogs/jonathan_kehayias/
        http://twitter.com/sqlsarg




        On Thu, Feb 2, 2012 at 10:12 PM, Jeffrey Garbus <jeff@...> wrote:
         

        I always test that out when I care; try the query, and see if you get any I/O. No I/O, short circuit worked.

         

        (SQL Server Usually gets it right)

         

        Have a great day!

         

        Jeff Garbus

         

        *** Check out my new blog, http://mssqlperformance.blogspot.com/ ***

        *** Check out my other new blog, http://aseperformance.blogspot.com/ ***

         

        My new book -- Microsoft Transact - SQL, The Definitive Guide -- Available NOW!

        Use coupon code "GARBUS" for a 35% discount when ordering

         

        813/966 1180 (mobile)

        813/641 3434 (office)

        813/354-4681 (fax)

        Jeffrey.Garbus (skype)

         

        From: tampasql@yahoogroups.com [mailto:tampasql@yahoogroups.com] On Behalf Of Jun Sato
        Sent: Tuesday, January 31, 2012 10:49 AM
        To: tampasql@yahoogroups.com
        Subject: [tampasql] TSQL short circuiting - safe to rely on?

         

         

        Hello all,

         

        Just wanted to get group opinion on something - is it safe to rely on short circuiting in IF's and CASE WHEN's?

        i.e. let's say I have something like:

         

        -- say yay if it has valid integer evaluating to 1234 (like 00001234)

        -- say nay if not.

         

        declare @myvar varchar(100)

        set @myvar = 'UNKNOWN' -- this should say nay

         

        select case when IsNumeric(@myvar) = 1 AND

                         Cast(@myvar as int) = 1234

               then 'yay' else 'nay' end

         

        The query above works, but what I'm thinking is...is TSQL allowed to evaluate conditions in any way it likes? i.e. will it ever do: 

         

        select case when Cast(@myvar as int) = 1234 AND

                         IsNumeric(@myvar) = 1

               then 'yay' else 'nay' end

         

        ..and break on type cast?

         

        As you know, in certain languages you can count on short circuiting. I can't seem to dig up official info on MS T-SQL - though I do know that ANSI SQL says it's implementation dependent so it's up to Microsoft as to how they go abuout it...my guess is that TSQL is really goot at short-circuiting for optimization reasons and it *usually* goes from left to right but not always, though I got nothing to verify that. Does anyone have definitive info on this?

         

        thanks and have a good day -

        Jun

         

         

         


      Your message has been successfully submitted and would be delivered to recipients shortly.