SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Back to basics


Back to basics

Author
Message
john.arnott
john.arnott
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2046 Visits: 3059
Here's another example of using the AND (&) operator to query an integer being used as a bit-map. You may have a set of integers, each representing the calendar for a month. You can set bits in each to indicate certain days, perhaps weekends and holidays. For this month, July 2013, here in the U.S. we would set the bits for the four weekends and for the holiday on the fourth of July. Then, we can use the & operator to query whether a particular date in the month is not a business day:


declare @monthCalendar int
set @monthCalendar = power(2,4) -- set bits for the holiday and the four weekends
+ power(2,6)
+ power(2,7)
+ power(2,13)
+ power(2,14)
+ power(2,20)
+ power(2,21)
+ power(2,27)
+ power(2,28)
declare @dateInMonth int
set @dateInMonth = 4 -- check this date in the month
declare @dateBitValue int
set @dateBitValue = power(2,@dateInMonth)
Select @monthCalendar as MonthCalendar -- this integer may have been stored in your db somewhere
,Case when @monthCalendar & @dateBitValue > 0 then 'Yes' Else 'No' end as DateIsSelected



Bangla
Bangla
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1707 Visits: 180
Nice question and discussion.....
wbc2
wbc2
SSC Eights!
SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)

Group: General Forum Members
Points: 803 Visits: 1053
I use this methodology to establish a system of Rights for Users in applications. Using BIGINT you can have a total of 63 different individual Rights assigned to a User all contained in a single BIGINT column value. Very compact and very fast for retrieval and using masks for determining if the User has a specific right or not.

Bill
vliet
vliet
SSC Veteran
SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)

Group: General Forum Members
Points: 222 Visits: 763
In SQL you have several options to associate a set with a row for a multi-valued attribute of an entity. The most obvious solution is a separate table that stores a foreign key to the original row along with a single (reference to a) value of the attribute in each row. You can add an XML column and store several attribute values inside it (even for different attributes) but storing and retrieving their value introduces some overhead and additional complexity to your queries. If the number of values for a single row is rather limited, a numbered column collection is also an option ([TelNr_1], [TelNr_2], [TelNr_3], ...) though I would personally never recommend this solution. If the total number of possible values is very limited you might use a bitmap where each bit represents a value and that bit is set if the multi-valued attribute contains that value. A separate table should map the individual bits to the actual values.

If you can live with the limitations of a bitmap (no foreign key and at most 64 values) it will save some storage but more important it allows you to do all kinds of set operations using simple bitwise operations. Checking wether the intersection of two sets is not empty requires no more than a bitwise and and a comparison. A bitwise or creates the union of two sets, an exclusive or the symmetric difference and an and with a not the difference. Allowing fast set operations makes these bitmaps extremely useful in authorization schemes. You should add the bitmap to your tool belt but use it with care, because it obscures the actual relations between tables and might cause serious trouble as soon as the number of possible values (roles for example) suddenly grows above that magic limit of 64.

Although there is no physical relation between the table with the bitmap attribute and the table that maps the individual bits (guarded by a foreign key), a logical relation between these tables does exist. Fortunately SQL Server allows us to define any join predicate we like, so instead of an eqi-join we may use a bitwise and as well: [OriginalTable] INNER JOIN [ValueTable] ON ([OriginalTable].[Bitmap] & [ValueTable].[IndividualBit]) <> 0. A subquery with a FOR XML clause and a few REPLACEs around it can turn your bitmap into a human-readable comma-separated list of attribute values.
PHYData DBA
PHYData DBA
SSC Eights!
SSC Eights! (955 reputation)SSC Eights! (955 reputation)SSC Eights! (955 reputation)SSC Eights! (955 reputation)SSC Eights! (955 reputation)SSC Eights! (955 reputation)SSC Eights! (955 reputation)SSC Eights! (955 reputation)

Group: General Forum Members
Points: 955 Visits: 537
Wow... One thing about Bitwise operaters is certain.
It is easy to read these posts and know who understands them completely and who is lost.
This would make for a great Article... Especialy the is BIGINT supported part....
Hrmmmmm Smooooth
sergiu0303
sergiu0303
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 112
I think that the best example here is @@OPTIONS.You do need the Bitwise AND operator if you're using it.
Revenant
Revenant
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7511 Visits: 4865
PHYData DBA (7/3/2013)
Wow... One thing about Bitwise operaters is certain.
It is easy to read these posts and know who understands them completely and who is lost.
This would make for a great Article... Especialy the is BIGINT supported part....
Hrmmmmm Smooooth

I wrote this QotD when I found that one of the members of my team did not understand that you could do AND on operands of a different length. I thought that the SQLServerCentral audience could benefit, and I hope we all did.
;-)
sqlnaive
sqlnaive
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4371 Visits: 2774
It was a good basic question. :-)
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33632 Visits: 18560
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


Another good example is with Reporting Services and Subscriptions



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

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