Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Back to basics


Back to basics

Author
Message
Danny Ocean
Danny Ocean
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1350 Visits: 1549
raulggonzalez (7/2/2013)
Danny Ocean (7/1/2013)
Good question. But i never feel to use "Bitwise AND" (&) in real working scenario. It will good, if anyone come with some real working example. :-)


Hi, another example where BIT comparison is useful, msdb..sysschedules keeps the freq_interval in bitwise value Smile


use [msdb]
go
create table #DaysOfWeekBitWise(
[bitValue] [tinyint] NOT NULL PRIMARY KEY,
[name] [varchar](10) NULL,
)
go
insert into #DaysOfWeekBitWise ([bitValue], [name])
values (1, N'Sunday')
, (2, N'Monday')
, (4, N'Tuesday')
, (8, N'Wednesday')
, (16, N'Thursday')
, (32, N'Friday')
, (64, N'Saturday')
go

select j.name
, case when j.enabled = 1 then 'Yes' else 'No' end as enabled
, jsch.next_run_date
, jsch.next_run_time
--, jst.*
, s.freq_interval
, ISNULL( STUFF( (SELECT N', ' + name FROM #DaysOfWeekBitWise AS B WHERE B.bitValue & s.freq_interval = B.bitValue FOR XML PATH('') ), 1, 2, '' ), 'None' ) AS backup_schedule
from msdb.dbo.sysjobs as j
left join msdb.dbo.sysjobschedules as jsch
on jsch.job_id = j.job_id
left join msdb.dbo.sysschedules as s
on s.schedule_id = jsch.schedule_id
order by j.name
go
drop table #DaysOfWeekBitWise
go



Cheers


Thanks raulggonzalez :-)

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
www.GrowWithSql.com
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10765 Visits: 12019
Good question, genuinely back to basics, but as your int value was 32767 wouldn't 32512 have been a better distractor than 65280?

Tom

TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10765 Visits: 12019
Patibandla (7/2/2013)
What if i define @S as BIGINT

According to BOL, [Bitwise Operations (Transact-SQL) bitwise operations don't support bigint; in practise (in SQL 2012 anyway) you can mix and match bit, tinyint, smallint, int, and bigint freely with bitwise AND, bitwise OR, and bitwise XOR. I suspect that this is an error in BOL and bigint actually is supported, but it would be risky to use bigint with bitwise operators for production without confirmation from Microsoft that this is the case. There's a community comment on the page that saying that bigint works, but MS don't appear to police those at all.

Tom

Dana Medley
Dana Medley
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2316 Visits: 1691
Thanks for the question!



Everything is awesome!
JAZZ Master
JAZZ Master
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1395 Visits: 857
martin.whitton (7/2/2013)
Danny Ocean (7/1/2013)
Good question. But i never feel to use "Bitwise AND" (&) in real working scenario. It will good, if anyone come with some real working example. :-)

It provides a neat way of sending multiple options as a single integer parameter.

For example, let's say you have a table called "Locations" with 2 columns, LocationID and Location, containing the following values:

LocationID Location
1 London
2 Dublin
4 Paris
8 Berlin
16 New York
etc...

Then you could select any combination of Location values as follows:

declare @Selection int;
set @Selection=10; -- (this will select Dublin and Paris, because Dublin's LocationID plus Paris's LocationID equals 10)

select
Location
from
Locations
where
LocationId & @Selection>0;





I'm confused. Doesn't 2 (Dublin) and 8 (Berlin) equal 10 not 2 (Dublin) and 4 (Paris)? What am I missing?
sqldoubleg
sqldoubleg
SSC Eights!
SSC Eights! (904 reputation)SSC Eights! (904 reputation)SSC Eights! (904 reputation)SSC Eights! (904 reputation)SSC Eights! (904 reputation)SSC Eights! (904 reputation)SSC Eights! (904 reputation)SSC Eights! (904 reputation)

Group: General Forum Members
Points: 904 Visits: 1345
Danny Ocean (7/2/2013)
[quote]raulggonzalez (7/2/2013)
[quote]Danny Ocean (7/1/2013)Thanks raulggonzalez :-)


You're welcome!

but please note that the script would give you correct results only when freq_interval = 8

http://msdn.microsoft.com/en-us/library/ms178644.aspx

Cheers
martin.whitton
martin.whitton
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1289 Visits: 1763
JAZZ Master (7/2/2013)
martin.whitton (7/2/2013)
Danny Ocean (7/1/2013)
Good question. But i never feel to use "Bitwise AND" (&) in real working scenario. It will good, if anyone come with some real working example. :-)

It provides a neat way of sending multiple options as a single integer parameter.

For example, let's say you have a table called "Locations" with 2 columns, LocationID and Location, containing the following values:

LocationID Location
1 London
2 Dublin
4 Paris
8 Berlin
16 New York
etc...

Then you could select any combination of Location values as follows:

declare @Selection int;
set @Selection=10; -- (this will select Dublin and Paris, because Dublin's LocationID plus Paris's LocationID equals 10)

select
Location
from
Locations
where
LocationId & @Selection>0;





I'm confused. Doesn't 2 (Dublin) and 8 (Berlin) equal 10 not 2 (Dublin) and 4 (Paris)? What am I missing?

Sorry Blush

You're absolutely right - I should have said Dublin and Berlin.
Revenant
Revenant
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5827 Visits: 4720
Patibandla (7/2/2013)
What if i define @S as BIGINT

Can you give me a real time scenario as in this operator would be useful. i am just a bit curious as i have never used used it.

If you check out the Books Online entry for the sysschedules table (2012),

http://msdn.microsoft.com/en-us/library/ms178644.aspx

you’ll notice the following statement:

freq_interval is one or more of the following:
1 = Sunday
2 = Monday
4 = Tuesday
8 = Wednesday
16 = Thursday
32 = Friday
64 = Saturday

It means that if my job is to run on Tuesday and Saturday, the value will be 68. On Tuesday you would test it with a mask 4, on Saturday with 64. Test on say Wednesday (mask 8) will result in zero, meaning "don't run."
Revenant
Revenant
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5827 Visits: 4720
L' Eomot Inversé (7/2/2013)
Good question, genuinely back to basics, but as your int value was 32767 wouldn't 32512 have been a better distractor than 65280?

Yes, it probably would. :-)
JAZZ Master
JAZZ Master
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1395 Visits: 857
martin.whitton (7/2/2013)
JAZZ Master (7/2/2013)
martin.whitton (7/2/2013)
Danny Ocean (7/1/2013)
Good question. But i never feel to use "Bitwise AND" (&) in real working scenario. It will good, if anyone come with some real working example. :-)

It provides a neat way of sending multiple options as a single integer parameter.

For example, let's say you have a table called "Locations" with 2 columns, LocationID and Location, containing the following values:




I'm confused. Doesn't 2 (Dublin) and 8 (Berlin) equal 10 not 2 (Dublin) and 4 (Paris)? What am I missing?

Sorry Blush

You're absolutely right - I should have said Dublin and Berlin.

Oh good. I was beginning to think I did not understand bitwise AND at all!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search