How to Decipher @@Options

  • jonfrisbee

    Old Hand

    Points: 354

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

  • DesNorton

    SSC-Insane

    Points: 23084

    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);
  • John N Hick

    Ten Centuries

    Points: 1315

    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)='
    1 DISABLE_DEF_CNST_CHK 0
    2 IMPLICIT_TRANSACTIONS 0
    4 CURSOR_CLOSE_ON_COMMIT 0
    8 ANSI_WARNINGS 0
    16 ANSI_PADDING 0
    32 ANSI_NULLS 0
    64 ARITHABORT 0
    128 ARITHIGNORE 0
    256 QUOTED_IDENTIFIER 0
    512 NOCOUNT 0
    1024 ANSI_NULL_DFLT_ON 0
    2048 ANSI_NULL_DFLT_OFF 0
    4096 CONCAT_NULL_YIELDS_NULL 0
    8192 NUMERIC_ROUNDABORT 0
    16384 XACT_ABORT 0
    '
    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 8 months, 4 weeks ago by  John N Hick.
  • Joseph M. Steinbrunner

    Mr or Mrs. 500

    Points: 503

    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.

  • thisisfutile

    Hall of Fame

    Points: 3527

    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.

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

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