Bitmasking Solution For Thorny SQL Set Operation

My previous post outlined a tricky little SQL query problem dealing with set intersection. In addition to the solution found by Hans, I thought for completeness I would post a little scalar function that does a bitmask comparison to achieve the same result – with the notable, and possibly untenable solution that it limits the number of categories in your database to 64 (the number of bits in a BIGINT).

create function IsMember
@personid int,
@groupid int
returns int
declare @ret bigint
declare @personsum bigint
select @personsum = (select sum(power(2, pc.categoryid - 1))
from personcategory pc
where pc.personid = @personid)
declare @groupsum bigint
select @groupsum = (select sum(power(2, gc.categoryid - 1))
from groupcategory gc
where gc.groupid = @groupid)
if ((@personsum & @groupsum) = @personsum)
select @ret = 1
select @ret = 0
return @ret


0 Responses to “Bitmasking Solution For Thorny SQL Set Operation”

  1. Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

TwitterCounter for @anthonyrstevens
Add to Technorati Favorites

RSS Feed

View Anthony Stevens's profile on LinkedIn

%d bloggers like this: