Bookmarks and browser "Back" button behavior.

I am using a lot of "BookmarkIds" and "Jump To Bookmarks" in a certain
report to Navigate around. There is a menu at the top of the report
that allows you to hyperlink down to more detail below. Problem is
this: When I hyperlink from the menu (at the top of the report) down to
more detail, I expect to be able to hit the Back button in my browser,
and return to the original spot in the report. Well, nothing happens
when I hit the browsers Back button. However, if I Export this report
to an MHTML file, the MTHML file works as desired (although it seems to
redisplay the report rather than go to previous link). One more
thing... I am pretty sure this WAS working at one time. Has anyone else
had similar trouble?I have more info on this problem. After exporting to an MHTML file, the
browser Back button works for the first few times I use it, then it
simply stops working until I exit the report and come back in. It is
not grayed out, it just stops going back to the top of the report when
pressed.
Thanks.
Eric

Blocking updates of a table

Hi,
I've been trying to design a way for me to issue a transaction that:

Block all inserts on a table when row X has a certain value (call it A)
Add a row to the table with row X containing A Add rows to another table Unblock inserts Commit transaction

Is this possible? Can anyone give me some pointers as to what to do?
Thanks in advance!

For (1) and (2)
While inserting the row that has a certain value (A), you can request a TABLOCKX. This will acquire X lock on the table and there by block other inserts by other concurrent transactions.

For (3): Is it like any other insert to another table? In that case, nothing special needs to be done

Thanks
Sunil Agarwal

|||For 3 that is what I meant.
For 1 and 2, it sounds like TABLOCKX locks the whole table exclusively (from what I can find in Books Online) - have I read it correctly? Is there any way to block other transactions from inserting rows (while the first transaction is not yet commited) when a table key column (A in my original post) is a certain value (X in my original post) - rather than just locking the whole table?
Thanks for your help!
|||For 1 and 2, it sounds like TABLOCKX locks the whole table exclusively (from what I can find in Books Online) - have I read it correctly

sunil> yes

Is there any way to block other transactions from inserting rows (while the first transaction is not yet commited) when a table key column (A in my original post) is a certain value (X in my original post) - rather than just locking the whole table?

sunila> if you only want to block inserts by other transactions under the condition you have mentioned but allow updates/selects, then there is no way.
thanks,|||I assume you only want to block Inserts but not Updates, Deletes and SELECTS.

You could try using an Insert trigger that would rollback the other transactions until a condition has been achieved.

blocking and deadlock detection

I'm using sql 2005 standard. There's a that a 3rd party app that I suspect
may be causing either some sort of block or deadlock when certain operations
are performed. Can anyone suggest a good way of configuring the profiler (or
any other method) to help in identifying problem spids?
tia
MG
Have a look here: http://support.microsoft.com/default.aspx/kb/271509
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Hurme" <michael.geles@.thomson.com> wrote in message
news:6927521C-B6AC-4B6F-B506-4D29039AE885@.microsoft.com...
> I'm using sql 2005 standard. There's a that a 3rd party app that I suspect
> may be causing either some sort of block or deadlock when certain
> operations
> are performed. Can anyone suggest a good way of configuring the profiler
> (or
> any other method) to help in identifying problem spids?
> tia
> --
> MG
|||You can also enable a trace flag to get detailed information about
deadlocks. See here: http://msdn2.microsoft.com/en-us/library/ms178104.aspx
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eCd19JDaIHA.220@.TK2MSFTNGP04.phx.gbl...
> Have a look here: http://support.microsoft.com/default.aspx/kb/271509
>
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Hurme" <michael.geles@.thomson.com> wrote in message
> news:6927521C-B6AC-4B6F-B506-4D29039AE885@.microsoft.com...
>
|||Hi
http://blogs.msdn.com/bartd/archive/2006/09/09/747119.aspx
http://blogs.msdn.com/bartd/archive/2006/09/25/770928.aspx
"Hurme" <michael.geles@.thomson.com> wrote in message
news:6927521C-B6AC-4B6F-B506-4D29039AE885@.microsoft.com...
> I'm using sql 2005 standard. There's a that a 3rd party app that I suspect
> may be causing either some sort of block or deadlock when certain
> operations
> are performed. Can anyone suggest a good way of configuring the profiler
> (or
> any other method) to help in identifying problem spids?
> tia
> --
> MG

Blocking

Is there a way to setup SQL server to automatically kill a
blocking process after a certain time frame (after maybe 5
Minutes for example)?
We are running SQL Server 2000 service pack 3a.
I second this question! Anyone have an easy solution for this?
will
"Wendy" wrote:

> Is there a way to setup SQL server to automatically kill a
> blocking process after a certain time frame (after maybe 5
> Minutes for example)?
> We are running SQL Server 2000 service pack 3a.
>
|||There is no way to do this automatically in SQL Server 2000. You can set up
a job that inspects sysprocesses periodically, looking at who is blocked,
and the wait_time, and then see who has blocked them. But you'll want to see
if the blocker is also being blocked, before indiscriminately issuing KILLs.
In fact, anything 'automatic' might have a problem with killing the 'wrong'
process.
SQL 2005 allows you to configure a 'blocked process threshold', to generate
an event when someone is blocked longer than the amount of time you
configure. So it will save you the periodic scanning of sysprocesses, but
you'll still have to apply some logic to figure out who you want to KILL (if
anyone) when a process does exceed that threshold.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"we7313" <we7313@.discussions.microsoft.com> wrote in message
news:D235A6DC-59B4-4997-B376-146D8666DE9A@.microsoft.com...
>I second this question! Anyone have an easy solution for this?
> --
> will
>
> "Wendy" wrote:
>

Blocking

Is there a way to setup SQL server to automatically kill a
blocking process after a certain time frame (after maybe 5
Minutes for example)?
We are running SQL Server 2000 service pack 3a.I second this question! Anyone have an easy solution for this?
--
will
"Wendy" wrote:

> Is there a way to setup SQL server to automatically kill a
> blocking process after a certain time frame (after maybe 5
> Minutes for example)?
> We are running SQL Server 2000 service pack 3a.
>|||There is no way to do this automatically in SQL Server 2000. You can set up
a job that inspects sysprocesses periodically, looking at who is blocked,
and the wait_time, and then see who has blocked them. But you'll want to see
if the blocker is also being blocked, before indiscriminately issuing KILLs.
In fact, anything 'automatic' might have a problem with killing the 'wrong'
process.
SQL 2005 allows you to configure a 'blocked process threshold', to generate
an event when someone is blocked longer than the amount of time you
configure. So it will save you the periodic scanning of sysprocesses, but
you'll still have to apply some logic to figure out who you want to KILL (if
anyone) when a process does exceed that threshold.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"we7313" <we7313@.discussions.microsoft.com> wrote in message
news:D235A6DC-59B4-4997-B376-146D8666DE9A@.microsoft.com...
>I second this question! Anyone have an easy solution for this?
> --
> will
>
> "Wendy" wrote:
>
>

Blocking

Is there a way to setup SQL server to automatically kill a
blocking process after a certain time frame (after maybe 5
Minutes for example)?
We are running SQL Server 2000 service pack 3a.I second this question! Anyone have an easy solution for this?
--
will
"Wendy" wrote:
> Is there a way to setup SQL server to automatically kill a
> blocking process after a certain time frame (after maybe 5
> Minutes for example)?
> We are running SQL Server 2000 service pack 3a.
>|||There is no way to do this automatically in SQL Server 2000. You can set up
a job that inspects sysprocesses periodically, looking at who is blocked,
and the wait_time, and then see who has blocked them. But you'll want to see
if the blocker is also being blocked, before indiscriminately issuing KILLs.
In fact, anything 'automatic' might have a problem with killing the 'wrong'
process.
SQL 2005 allows you to configure a 'blocked process threshold', to generate
an event when someone is blocked longer than the amount of time you
configure. So it will save you the periodic scanning of sysprocesses, but
you'll still have to apply some logic to figure out who you want to KILL (if
anyone) when a process does exceed that threshold.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"we7313" <we7313@.discussions.microsoft.com> wrote in message
news:D235A6DC-59B4-4997-B376-146D8666DE9A@.microsoft.com...
>I second this question! Anyone have an easy solution for this?
> --
> will
>
> "Wendy" wrote:
>> Is there a way to setup SQL server to automatically kill a
>> blocking process after a certain time frame (after maybe 5
>> Minutes for example)?
>> We are running SQL Server 2000 service pack 3a.
>

block all apps except for...

Is there a way to make it so that nobody can connect to sql unless they come
in through a certain app... without the use of Application Roles. ( my boss
doesnt want to have to pass in a password each time.)
TIA, ChrisR
On Wed, 15 Dec 2004 16:09:00 -0800, "ChrisR" <ChrisR@.noEmail.com>
wrote:
>Is there a way to make it so that nobody can connect to sql unless they come
>in through a certain app... without the use of Application Roles. ( my boss
>doesnt want to have to pass in a password each time.)
Can't you also do it with Windows domain logins, by defining a new
domain group and assigning just certain people to that group (role?),
and then giving only that group login permission in SQLServer?
I'm obviously not the expert on this, but I had the impression that
was also possible - it's application roles, but done at the network
security level, and no special passwords are required. If it's
possible at all!
J.
|||"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:ewSE$Ow4EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Is there a way to make it so that nobody can connect to sql unless they
come
> in through a certain app... without the use of Application Roles. ( my
boss
> doesnt want to have to pass in a password each time.)
Get a new boss. :-)

>
> TIA, ChrisR
>
|||The dilema with this is that I need to know how to do it for SQL Logins as
well.
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:t9m1s01oi6urm763144udojr98tsvr3eh6@.4ax.com... [vbcol=seagreen]
> On Wed, 15 Dec 2004 16:09:00 -0800, "ChrisR" <ChrisR@.noEmail.com>
> wrote:
come[vbcol=seagreen]
boss
> Can't you also do it with Windows domain logins, by defining a new
> domain group and assigning just certain people to that group (role?),
> and then giving only that group login permission in SQLServer?
> I'm obviously not the expert on this, but I had the impression that
> was also possible - it's application roles, but done at the network
> security level, and no special passwords are required. If it's
> possible at all!
> J.
>
|||If you want to secure the system from access outside of the application,
there are two methods outside of the use of Application Roles.
First, you should be using Windows Authentication. You can either grant
users access through the use of Windows Groups, which map to database roles,
or, have a single application login id, Windows Account. Your application
would then need to be set up as a server, use DCOM, or COM+ for the database
access components.
That's for the access. To keep users from accessing the data without using
the application, consider the exclusive use of stored procedures for all of
the data manipulation. Then for users with directly mapped access, through
individual logins or the use of Windows Groups, explicitly add these users
to the default db_denydatareader and db_denydatawriter database roles.
Sincerely,
Anthony Thomas

"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:ewSE$Ow4EHA.1408@.TK2MSFTNGP10.phx.gbl...
Is there a way to make it so that nobody can connect to sql unless they come
in through a certain app... without the use of Application Roles. ( my boss
doesnt want to have to pass in a password each time.)
TIA, ChrisR

block all apps except for...

Is there a way to make it so that nobody can connect to sql unless they come
in through a certain app... without the use of Application Roles. ( my boss
doesnt want to have to pass in a password each time.)
TIA, ChrisROn Wed, 15 Dec 2004 16:09:00 -0800, "ChrisR" <ChrisR@.noEmail.com>
wrote:
>Is there a way to make it so that nobody can connect to sql unless they come
>in through a certain app... without the use of Application Roles. ( my boss
>doesnt want to have to pass in a password each time.)
Can't you also do it with Windows domain logins, by defining a new
domain group and assigning just certain people to that group (role?),
and then giving only that group login permission in SQLServer?
I'm obviously not the expert on this, but I had the impression that
was also possible - it's application roles, but done at the network
security level, and no special passwords are required. If it's
possible at all!
J.|||"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:ewSE$Ow4EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Is there a way to make it so that nobody can connect to sql unless they
come
> in through a certain app... without the use of Application Roles. ( my
boss
> doesnt want to have to pass in a password each time.)
Get a new boss. :-)
>
> TIA, ChrisR
>|||The dilema with this is that I need to know how to do it for SQL Logins as
well.
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:t9m1s01oi6urm763144udojr98tsvr3eh6@.4ax.com...
> On Wed, 15 Dec 2004 16:09:00 -0800, "ChrisR" <ChrisR@.noEmail.com>
> wrote:
> >Is there a way to make it so that nobody can connect to sql unless they
come
> >in through a certain app... without the use of Application Roles. ( my
boss
> >doesnt want to have to pass in a password each time.)
> Can't you also do it with Windows domain logins, by defining a new
> domain group and assigning just certain people to that group (role?),
> and then giving only that group login permission in SQLServer?
> I'm obviously not the expert on this, but I had the impression that
> was also possible - it's application roles, but done at the network
> security level, and no special passwords are required. If it's
> possible at all!
> J.
>|||If you want to secure the system from access outside of the application,
there are two methods outside of the use of Application Roles.
First, you should be using Windows Authentication. You can either grant
users access through the use of Windows Groups, which map to database roles,
or, have a single application login id, Windows Account. Your application
would then need to be set up as a server, use DCOM, or COM+ for the database
access components.
That's for the access. To keep users from accessing the data without using
the application, consider the exclusive use of stored procedures for all of
the data manipulation. Then for users with directly mapped access, through
individual logins or the use of Windows Groups, explicitly add these users
to the default db_denydatareader and db_denydatawriter database roles.
Sincerely,
Anthony Thomas
"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:ewSE$Ow4EHA.1408@.TK2MSFTNGP10.phx.gbl...
Is there a way to make it so that nobody can connect to sql unless they come
in through a certain app... without the use of Application Roles. ( my boss
doesnt want to have to pass in a password each time.)
TIA, ChrisR

Blobs in SQL

Can anyone tell me what the pros/cons are of using blobs in sql?
We are looking at scanning certain paperwork and storing it with the
appropriate records in our database. The two options are to store the images
as blobs or to store them as file system objects with a referral pointer in
the db.
I have heard a number of different people say that storing blobs is
inherently evil but they haven't explained why that is.
Any help will be appreciated.
This is one of those topics that has been debated countless times, and there
is no universal truth.
Storing in the database
-transactional integrity
-maintenance simplicity
Storing in the filesystem
-performance
-efficiency
-accessibility
"Jawallaby" wrote:

> Can anyone tell me what the pros/cons are of using blobs in sql?
> We are looking at scanning certain paperwork and storing it with the
> appropriate records in our database. The two options are to store the images
> as blobs or to store them as file system objects with a referral pointer in
> the db.
> I have heard a number of different people say that storing blobs is
> inherently evil but they haven't explained why that is.
> Any help will be appreciated.
>
|||Thanks Dennis.
When you list performance as a pro of using the filesystem what are the
factors that increase performance? What is the performance hit when storing
in the db?
Most if not all of these files will be less than 1mb. There is a potential
for roughly 20 different files to be stored for each person/record in the db
and there are roughly 46000 records active.
I am trying to decide if this is worth worrying about or not and just don't
know enough about blobs in sql.
"Dennis Forbes" wrote:
[vbcol=seagreen]
> This is one of those topics that has been debated countless times, and there
> is no universal truth.
> Storing in the database
> -transactional integrity
> -maintenance simplicity
> Storing in the filesystem
> -performance
> -efficiency
> -accessibility
> "Jawallaby" wrote:
|||Dennis,
We've been scanning and storing document images as 'blobs' in our Informix
db
basically for years. We opted for the transactional integrity &
maintenance simplicity
over the performance and efficiency. Most of our scanned .pdf documents
are
fairly small and average around 100k.
We're not a highly transactional environment and we may scan (insert or
update)
from 50 to 100 documents a day.
Using this method we totally eliminate any file naming concerns, pathing,
directory
file limit issues, etc. (since I've never tried to maintain a pointer /
file based I can't
really speak to the up and down side of the method)
We're currently in the process of migrating to Sql Server and I've verified
that the
Sql Server Image column works in a very similar manner to the Informix Byte
(blob)
columns. The up side of going to Sql Server is that we can now manage our
image table insert, updates & deletes using stored procedures. We don't
have that
ability with Informix using oledb connectivity.
You can see the system in action at the following urls by clicking the blue
permit
or certificate numbers at
http://apps.wrd.state.or.us/apps/wr/...ID&snpid=64734
The downside can be database backups ... we currently have around 80G of
scanned imagesand a full db backup takes about 3 to 4 hrs to DLT tape.
Hope this helps.
Barry
in Oregon
"Jawallaby" <Jawallaby@.discussions.microsoft.com> wrote in message
news:24797C1E-595B-4C48-A4C7-AE264E8423BA@.microsoft.com...[vbcol=seagreen]
> Thanks Dennis.
> When you list performance as a pro of using the filesystem what are the
> factors that increase performance? What is the performance hit when
> storing
> in the db?
> Most if not all of these files will be less than 1mb. There is a potential
> for roughly 20 different files to be stored for each person/record in the
> db
> and there are roughly 46000 records active.
> I am trying to decide if this is worth worrying about or not and just
> don't
> know enough about blobs in sql.
>
> "Dennis Forbes" wrote:

Blobs in SQL

Can anyone tell me what the pros/cons are of using blobs in sql?
We are looking at scanning certain paperwork and storing it with the
appropriate records in our database. The two options are to store the images
as blobs or to store them as file system objects with a referral pointer in
the db.
I have heard a number of different people say that storing blobs is
inherently evil but they haven't explained why that is.
Any help will be appreciated.This is one of those topics that has been debated countless times, and there
is no universal truth.
Storing in the database
-transactional integrity
-maintenance simplicity
Storing in the filesystem
-performance
-efficiency
-accessibility
"Jawallaby" wrote:

> Can anyone tell me what the pros/cons are of using blobs in sql?
> We are looking at scanning certain paperwork and storing it with the
> appropriate records in our database. The two options are to store the imag
es
> as blobs or to store them as file system objects with a referral pointer i
n
> the db.
> I have heard a number of different people say that storing blobs is
> inherently evil but they haven't explained why that is.
> Any help will be appreciated.
>

Blobs in SQL

Can anyone tell me what the pros/cons are of using blobs in sql?
We are looking at scanning certain paperwork and storing it with the
appropriate records in our database. The two options are to store the images
as blobs or to store them as file system objects with a referral pointer in
the db.
I have heard a number of different people say that storing blobs is
inherently evil but they haven't explained why that is.
Any help will be appreciated.This is one of those topics that has been debated countless times, and there
is no universal truth.
Storing in the database
-transactional integrity
-maintenance simplicity
Storing in the filesystem
-performance
-efficiency
-accessibility
"Jawallaby" wrote:
> Can anyone tell me what the pros/cons are of using blobs in sql?
> We are looking at scanning certain paperwork and storing it with the
> appropriate records in our database. The two options are to store the images
> as blobs or to store them as file system objects with a referral pointer in
> the db.
> I have heard a number of different people say that storing blobs is
> inherently evil but they haven't explained why that is.
> Any help will be appreciated.
>|||Thanks Dennis.
When you list performance as a pro of using the filesystem what are the
factors that increase performance? What is the performance hit when storing
in the db?
Most if not all of these files will be less than 1mb. There is a potential
for roughly 20 different files to be stored for each person/record in the db
and there are roughly 46000 records active.
I am trying to decide if this is worth worrying about or not and just don't
know enough about blobs in sql.
"Dennis Forbes" wrote:
> This is one of those topics that has been debated countless times, and there
> is no universal truth.
> Storing in the database
> -transactional integrity
> -maintenance simplicity
> Storing in the filesystem
> -performance
> -efficiency
> -accessibility
> "Jawallaby" wrote:
> > Can anyone tell me what the pros/cons are of using blobs in sql?
> >
> > We are looking at scanning certain paperwork and storing it with the
> > appropriate records in our database. The two options are to store the images
> > as blobs or to store them as file system objects with a referral pointer in
> > the db.
> >
> > I have heard a number of different people say that storing blobs is
> > inherently evil but they haven't explained why that is.
> >
> > Any help will be appreciated.
> >
> >|||Dennis,
We've been scanning and storing document images as 'blobs' in our Informix
db
basically for years. We opted for the transactional integrity &
maintenance simplicity
over the performance and efficiency. Most of our scanned .pdf documents
are
fairly small and average around 100k.
We're not a highly transactional environment and we may scan (insert or
update)
from 50 to 100 documents a day.
Using this method we totally eliminate any file naming concerns, pathing,
directory
file limit issues, etc. (since I've never tried to maintain a pointer /
file based I can't
really speak to the up and down side of the method)
We're currently in the process of migrating to Sql Server and I've verified
that the
Sql Server Image column works in a very similar manner to the Informix Byte
(blob)
columns. The up side of going to Sql Server is that we can now manage our
image table insert, updates & deletes using stored procedures. We don't
have that
ability with Informix using oledb connectivity.
You can see the system in action at the following urls by clicking the blue
permit
or certificate numbers at
http://apps.wrd.state.or.us/apps/wr/wrinfo/wrinfo.php?search_type=SNPID&snpid=64734
The downside can be database backups ... we currently have around 80G of
scanned imagesand a full db backup takes about 3 to 4 hrs to DLT tape.
Hope this helps.
Barry
in Oregon
"Jawallaby" <Jawallaby@.discussions.microsoft.com> wrote in message
news:24797C1E-595B-4C48-A4C7-AE264E8423BA@.microsoft.com...
> Thanks Dennis.
> When you list performance as a pro of using the filesystem what are the
> factors that increase performance? What is the performance hit when
> storing
> in the db?
> Most if not all of these files will be less than 1mb. There is a potential
> for roughly 20 different files to be stored for each person/record in the
> db
> and there are roughly 46000 records active.
> I am trying to decide if this is worth worrying about or not and just
> don't
> know enough about blobs in sql.
>
> "Dennis Forbes" wrote:
>> This is one of those topics that has been debated countless times, and
>> there
>> is no universal truth.
>> Storing in the database
>> -transactional integrity
>> -maintenance simplicity
>> Storing in the filesystem
>> -performance
>> -efficiency
>> -accessibility
>> "Jawallaby" wrote:
>> > Can anyone tell me what the pros/cons are of using blobs in sql?
>> >
>> > We are looking at scanning certain paperwork and storing it with the
>> > appropriate records in our database. The two options are to store the
>> > images
>> > as blobs or to store them as file system objects with a referral
>> > pointer in
>> > the db.
>> >
>> > I have heard a number of different people say that storing blobs is
>> > inherently evil but they haven't explained why that is.
>> >
>> > Any help will be appreciated.
>> >
>> >

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))
ENDYou 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...
>> 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
>