How to Decipher @@Options

  • Comments posted to this topic are about the item How to Decipher @@Options

  • All of those UNIONs are creating an insane query plan.

    The following code will produce almost the same result, with a MUCH better query plan.

    DECLARE @options int = @@OPTIONS;

    SELECT so.SetOption
    , OptionState = CASE WHEN so.SetValue & @options = so.SetValue THEN 'ON' ELSE 'OFF' END
    , BinaryValue = so.SetValue
    FROM (VALUES
    ( CAST(1 AS int), 'DISABLE_DEF_CNST_CHK' )
    , ( 2, 'IMPLICIT_TRANSACTIONS' )
    , ( 4, 'CURSOR_CLOSE_ON_COMMIT' )
    , ( 8, 'ANSI_WARNINGS' )
    , ( 16, 'ANSI_PADDING' )
    , ( 32, 'ANSI_NULLS' )
    , ( 64, 'ARITHABORT' )
    , ( 128, 'ARITHIGNORE' )
    , ( 256, 'QUOTED_IDENTIFIER' )
    , ( 512, 'NOCOUNT' )
    , ( 1024, 'ANSI_NULL_DFLT_ON' )
    , ( 2048, 'ANSI_NULL_DFLT_OFF' )
    , ( 4096, 'CONCAT_NULL_YIELDS_NULL' )
    , ( 8192, 'NUMERIC_ROUNDABORT' )
    , ( 16384, 'XACT_ABORT' )
    ) AS so(SetValue, SetOption);
  • Thank you, @jonfrisbee, for sharing.  Based on your article, I whipped up this quickie:

    declare @SepRepl char(1)=''
    declare @ColNums int=3
    declare @ArgValList varchar(max)='
    1DISABLE_DEF_CNST_CHK0
    2IMPLICIT_TRANSACTIONS0
    4CURSOR_CLOSE_ON_COMMIT0
    8ANSI_WARNINGS0
    16ANSI_PADDING0
    32ANSI_NULLS0
    64ARITHABORT0
    128ARITHIGNORE0
    256QUOTED_IDENTIFIER0
    512NOCOUNT0
    1024ANSI_NULL_DFLT_ON0
    2048ANSI_NULL_DFLT_OFF0
    4096CONCAT_NULL_YIELDS_NULL0
    8192NUMERIC_ROUNDABORT0
    16384XACT_ABORT0
    '
    declare @RowDelim char(2)=char(13)+char(10)
    ,@ColDelim char(1)=char(9)
    ,@Sep char(1)=','
    set @ArgValList=replace(@ArgValList,@Sep,@SepRepl)
    set @ArgValList=replace(@Sep+replace(replace(replace(replace(@ArgValList,@ColDelim,@Sep),@RowDelim,@Sep),char(9),''),@Sep+@Sep,@Sep)+@Sep,@Sep+@Sep,@Sep)
    declare @opts int=@@options;
    declare @vals table(bitwise int not null primary key clustered,opt varchar(2555),setting bit default 0)
    ;with integers(n) as (
    select top (len(@ArgValList)) row_number() over (order by (select null))
    from(values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) [1](n)-- 10^1
    cross join (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) [2](n)-- 10^2
    cross join (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) _(n)-- 10^3
    ),splitter(RowNum,Value) as (
    select row_number() over (order by n)-1
    ,substring(@ArgValList,n+1,charindex(@Sep,@ArgValList,n+1)-(n+1))
    from integers
    where n<len(@ArgValList)
    and substring(@ArgValList,n,1)=@Sep
    )
    insert into @vals
    select bitwise=max(case when RowNum%@ColNums=0 then replace(Value,@SepRepl,@Sep) end)
    ,opt=max(case when RowNum%@ColNums=1 then replace(Value,@SepRepl,@Sep) end)
    ,setting=cast(max(case when RowNum%@ColNums=2 then replace(Value,@SepRepl,@Sep) end) as int)
    from splitter
    group by RowNum/@ColNums

    update v
    set setting=iif(@opts & bitwise=0,0,1)
    from @vals v

    select *
    from @vals

    • This reply was modified 2 years, 10 months ago by  John N Hick.
  • All of those UNIONs are creating an insane query plan.

    The following code will produce almost the same result, with a MUCH better query plan.

    If you change UNION to UNION ALL for each one in the original query, it comes back as an even better query plan than the one DesNorton proposes.

  • Joseph M. Steinbrunner wrote:

    If you change UNION to UNION ALL for each one in the original query, it comes back as an even better query plan than the one DesNorton proposes.

    Good eye.  The first thing I do when I see a 'UNION' is ask, "can UNION ALL be used".  It's always faster because the optimizer doesn't have to do comparisons.

  • @@ meta data tags are defined over the address level of sql decision?

    will it change the temp level or master level attributes for the execution.

  • Thank you @jonfrisbee and the other contributors for this useful information all in one place. Adding your stored procedure to my "toolkit"!

    Nice to see an article about real bitmaps, before Microsoft hijacked the word for some graphics stuff or other 🙂

    • This reply was modified 1 year, 8 months ago by  DaveBoltman.
    • This reply was modified 1 year, 8 months ago by  DaveBoltman.
  • The way that I normally did this type of query (since the "Sybase" days) is to do the following:

    SELECT
    name,
    (case when @@options & number != 0 then 'ON' else 'OFF' end) as status
    FROM
    master..spt_values
    WHERE
    type = 'SOP'
    AND number != 0

    Recently, I re-discovered that osql.exe turns off Quoted Identifiers by default unless you use the -I (Note: capital i) switch.  (Lower case -i specifies the input file.)

    Also...  Over the years, I've used spt_values to get at lots of cool information.  Of course, Microsoft threatens to drop this table...  (for the last 20 years...  🙂  )

     

  • jdvenice wrote:

    Also...  Over the years, I've used spt_values to get at lots of cool information.  Of course, Microsoft threatens to drop this table...  (for the last 20 years...  🙂  )

    See the article about the "fnTally" function at the similarly named link in my signature line below for a useful replacement the creates 0 reads.

    And, yea... that's one of my favorite methods, as well, especially when it comes to the bit mask for updated columns in triggers.

    For this one, though, the special "inline table" that Des Norton made for this is the way to go.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • DesNorton wrote:

    All of those UNIONs are creating an insane query plan.

    The following code will produce almost the same result, with a MUCH better query plan.

    DECLARE @options int = @@OPTIONS;

    SELECT so.SetOption
    , OptionState = CASE WHEN so.SetValue & @options = so.SetValue THEN 'ON' ELSE 'OFF' END
    , BinaryValue = so.SetValue
    FROM (VALUES
    ( CAST(1 AS int), 'DISABLE_DEF_CNST_CHK' )
    , ( 2, 'IMPLICIT_TRANSACTIONS' )
    , ( 4, 'CURSOR_CLOSE_ON_COMMIT' )
    , ( 8, 'ANSI_WARNINGS' )
    , ( 16, 'ANSI_PADDING' )
    , ( 32, 'ANSI_NULLS' )
    , ( 64, 'ARITHABORT' )
    , ( 128, 'ARITHIGNORE' )
    , ( 256, 'QUOTED_IDENTIFIER' )
    , ( 512, 'NOCOUNT' )
    , ( 1024, 'ANSI_NULL_DFLT_ON' )
    , ( 2048, 'ANSI_NULL_DFLT_OFF' )
    , ( 4096, 'CONCAT_NULL_YIELDS_NULL' )
    , ( 8192, 'NUMERIC_ROUNDABORT' )
    , ( 16384, 'XACT_ABORT' )
    ) AS so(SetValue, SetOption);

    Just one word for that... SPOT-ON.  And can be easily made into a system stored proc in master so it's available from any database.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • @jonfrisbee ,

    I can't fault someone that took the time to write an article to help others.  Hat's off to you for that.  But have a look at the way Des Norton did it.  He built a special purpose inline "Tally table" and used it as a high performance "pseudo cursor".

    Yep... I realize that this won't be used much and I'm not talking so much about what the end product does.  I'm talking about the technique for stuff used that works very well for very high hit ratio procs.  For an introduction into such a "pseudo-cursor", take a look at the last link in my signature line below for how a "Tally" table can be used to replace a lot of different kinds of loops and repetitive code, which is really a hard-coded loop.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 11 posts - 1 through 11 (of 11 total)

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