Bitwise in SQL Server

27 Nov 2008 10:58 PM

mr t

Ok, I am just exploring the Bitwise operations in SQL. I like the fact
that I can take a field called GroupID int, and store the Groups in
which the user is subscribed to. such as Group 1 and Group 16. and all
I have to do to check if the user is in that group, I do:

(GroupID&2)=2

That's kind of nice. But problem, I cannot create more than 32 groups
b/c of integer calculations limit.

What is the best way to go about this? Is there a trick to be able to
increase the options other than the following:

- BigInt: which will have its own limit in the end.
- Or creating a relationship Member_Group table as usual.

Thanks every genius person in advance.

27 Nov 2008 11:35 PM

Tom Moreau

I would actually parse it off into a table of subscribed groups and then use
relational division to determine if a user is a member of a specific set of
groups. Alternatively, if you are looking for membership in a single group,
then it's a simple SELECT.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

"mr t" <tasc***@gmail.com> wrote in message
news:84a6973b-625f-4345-9bf0-80d98833b3f5@u18g2000pro.googlegroups.com...

Ok, I am just exploring the Bitwise operations in SQL. I like the fact
that I can take a field called GroupID int, and store the Groups in
which the user is subscribed to. such as Group 1 and Group 16. and all
I have to do to check if the user is in that group, I do:

(GroupID&2)=2

That's kind of nice. But problem, I cannot create more than 32 groups
b/c of integer calculations limit.

What is the best way to go about this? Is there a trick to be able to
increase the options other than the following:

- BigInt: which will have its own limit in the end.
- Or creating a relationship Member_Group table as usual.

Thanks every genius person in advance.

28 Nov 2008 7:11 AM

Lutz Uhlmann

> That's kind of nice. But problem, I cannot create more than 32 groups
> b/c of integer calculations limit.
>
> What is the best way to go about this? Is there a trick to be able to
> increase the options other than the following:

How many groups you talking about???
Maybe use a second field ...

28 Nov 2008 1:06 PM

Fred

in

news:84a6973b-625f-4345-9bf0-80d98833b3f5@u18g2000pro.googlegroups.com,

mr t wrote :

Show quoteHide quote

> Ok, I am just exploring the Bitwise operations in SQL. I like the fact
> that I can take a field called GroupID int, and store the Groups in
> which the user is subscribed to. such as Group 1 and Group 16. and all
> I have to do to check if the user is in that group, I do:
>
> (GroupID&2)=2
>
> That's kind of nice. But problem, I cannot create more than 32 groups
> b/c of integer calculations limit.
>
> What is the best way to go about this? Is there a trick to be able to
> increase the options other than the following:
>
> - BigInt: which will have its own limit in the end.
> - Or creating a relationship Member_Group table as usual.

If you plan to use this in WHERE clauses, it's a false good idea as SQL
won't be able to optimize the query execution plan.
As Tom said, I also use the Member_Group table.
And if you are looking for something like row based access control, you
can have a look here :
http://www.microsoft.com/technet/prodtechnol/sql/2005/multisec.mspx

--
Fred
fole***@free.fr