bit mask with 300 flags

Hello,
I have 300 groups. Each user can or can not be associated with certain
group. I would like to represent group membership using corresponding flag
(on/off). Using integer dattatype it will look like : G1- 1, G2 - 2, G3 -
4....
If user is member of group of 1,2 and 3 it will be represented by number 7,
etc.
It is power of 2.With BIGINT data type I can only store up to 63 different
groups. I need to store up to 300 groups.
I could split groups into several integer fields but I was hoping to use
single VARBINARY field.
I am trying to store all flags in VARBINARY(300) field. The problem is
that when populating groups table (see below), SQL server can only perform
bitwise operations when one of the agruments is of integer type. I am trying
to loop through all 300 groups and populate them with power of 2.
I run into the same limitation (no more than 63 groups). Is this possible
to store 300 flags for each groups ina single field?
Thanks,
Igor
CREATE TABLE groups_bin
(
group_id SMALLINT ,
group_name VARCHAR(100),
group_bit_mask VARBINARY(2)
)
DECLARE @.binBitMask VARBINARY(255),@.intID INT,@.intMaxID INT
SELECT @.binBitMask=0x0001,@.intID=1,@.intMaxID=300
WHILE @.intID<=@.intMaxID
BEGIN
INSERT INTO groups_bin(group_id,group_name,group_bit_mask)
VALUES(@.intID,'G'+CAST(@.intID AS VARCHAR(30)),@.binBitMask)
SELECT @.binBitMask=CAST(@.binBitMask*2 AS VARBINARY(255)),@.intID=@.intID+1
PRINT CAST(@.binBitMask*2 AS VARBINARY(255))
END
You really should normalize your data. The SQL Engine is optimized for
working with sets of data, not for manual parsing and manipulation of
individual bits in columns.
"Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
news:%23x%23fgOBMFHA.3340@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I have 300 groups. Each user can or can not be associated with certain
> group. I would like to represent group membership using corresponding flag
> (on/off). Using integer dattatype it will look like : G1- 1, G2 - 2, G3 -
> 4....
> If user is member of group of 1,2 and 3 it will be represented by number
> 7,
> etc.
> It is power of 2.With BIGINT data type I can only store up to 63
> different
> groups. I need to store up to 300 groups.
> I could split groups into several integer fields but I was hoping to use
> single VARBINARY field.
> I am trying to store all flags in VARBINARY(300) field. The problem is
> that when populating groups table (see below), SQL server can only perform
> bitwise operations when one of the agruments is of integer type. I am
> trying
> to loop through all 300 groups and populate them with power of 2.
> I run into the same limitation (no more than 63 groups). Is this possible
> to store 300 flags for each groups ina single field?
> Thanks,
> Igor
> CREATE TABLE groups_bin
> (
> group_id SMALLINT ,
> group_name VARCHAR(100),
> group_bit_mask VARBINARY(2)
> )
> DECLARE @.binBitMask VARBINARY(255),@.intID INT,@.intMaxID INT
> SELECT @.binBitMask=0x0001,@.intID=1,@.intMaxID=300
> WHILE @.intID<=@.intMaxID
> BEGIN
> INSERT INTO groups_bin(group_id,group_name,group_bit_mask)
> VALUES(@.intID,'G'+CAST(@.intID AS VARCHAR(30)),@.binBitMask)
> SELECT @.binBitMask=CAST(@.binBitMask*2 AS VARBINARY(255)),@.intID=@.intID+1
> PRINT CAST(@.binBitMask*2 AS VARBINARY(255))
> END
>
>
|||"Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
news:%23x%23fgOBMFHA.3340@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I have 300 groups. Each user can or can not be associated with certain
> group. I would like to represent group membership using corresponding flag
> (on/off). Using integer dattatype it will look like : G1- 1, G2 - 2, G3 -
> 4....
> If user is member of group of 1,2 and 3 it will be represented by number
> 7,
> etc.
> It is power of 2.With BIGINT data type I can only store up to 63
> different
> groups. I need to store up to 300 groups.
> I could split groups into several integer fields but I was hoping to use
> single VARBINARY field.
> I am trying to store all flags in VARBINARY(300) field. The problem is
> that when populating groups table (see below), SQL server can only perform
> bitwise operations when one of the agruments is of integer type. I am
> trying
> to loop through all 300 groups and populate them with power of 2.
> I run into the same limitation (no more than 63 groups). Is this possible
> to store 300 flags for each groups ina single field?
>
Is it really appropriate (or necessary) to enshrine the 300 groups in the
metadata?
The more usual model would be
create table Users(id int primary key, name varchar(50) not null)
create table Groups(id int primary key, name varchar(50) not null)
create table UserGroup
(
UserIDint not null references Users on delete cascade,
GroupID id not null references Groups on delete cascade,
constraint pk_UserGroup primary key (UserID,GroupID)
)
create index ix_UserGroup on UserGroup(GroupID)
If you do need to encode the 300 group memberships in a single row, either
because it's appropriate because the 300 groups are as unchanging as the
database schema, or necessary because of some crazy performance requirement,
why not store the 300 memberships in 300 columns?
create table Users
(
id int primary key,
name varchar(50) not null,
InGroup1 bit not null default 0,
InGroup2 bit not null default 0,
. . .
)
David
|||Replied in microsoft.public.sqlserver.programming.
Please do not post the same question indepenedently in multiple groups.
David Portas
SQL Server MVP
|||David,
I do not want to have 300 columns because of maintenance reason. Bitmask
approach will allow me to have certain degree of flexibility without need to
change schema (in case I will need to add new groups). For example, I can
create VARBINARY field with extra empty bits to reserve space for future
groups. My main objective is to save space. We will have millions of users.
UserGroup table will be huge if we use traditional relational approach.
Groups are very much static.
Any comments are welcome.
Igor
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:ef%23EjoBMFHA.3184@.TK2MSFTNGP09.phx.gbl...
> "Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
> news:%23x%23fgOBMFHA.3340@.TK2MSFTNGP14.phx.gbl...
> Is it really appropriate (or necessary) to enshrine the 300 groups in the
> metadata?
> The more usual model would be
> create table Users(id int primary key, name varchar(50) not null)
> create table Groups(id int primary key, name varchar(50) not null)
> create table UserGroup
> (
> UserIDint not null references Users on delete cascade,
> GroupID id not null references Groups on delete cascade,
> constraint pk_UserGroup primary key (UserID,GroupID)
> )
> create index ix_UserGroup on UserGroup(GroupID)
>
> If you do need to encode the 300 group memberships in a single row, either
> because it's appropriate because the 300 groups are as unchanging as the
> database schema, or necessary because of some crazy performance
> requirement, why not store the 300 memberships in 300 columns?
> create table Users
> (
> id int primary key,
> name varchar(50) not null,
> InGroup1 bit not null default 0,
> InGroup2 bit not null default 0,
> . . .
> )
> David
>