November 14, 2019 at 12:00 am
Comments posted to this topic are about the item How to Decipher @@Options
November 14, 2019 at 10:02 am
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);
November 14, 2019 at 3:07 pm
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
November 14, 2019 at 8:04 pm
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.
November 16, 2019 at 8:05 pm
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.
January 9, 2021 at 2:20 pm
@@ 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.
January 11, 2021 at 8:12 am
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 🙂
March 21, 2022 at 6:16 pm
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... 🙂 )
March 22, 2022 at 2:19 am
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
Change is inevitable... Change for the better is not.
March 22, 2022 at 2:26 am
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
Change is inevitable... Change for the better is not.
March 22, 2022 at 2:34 am
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
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply