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
as
begin
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
else
select @ret = 0
return @ret
end

Advertisements

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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s





%d bloggers like this: