200 Flags - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

200 Flags

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2002-06-21 : 08:07:28

New to SQL - I've got a set problem.

I need to track 200 items as true or false (1/0).

An array of 200 in a table would be great.

A set of 200 would be great.

I don't believe arrays of bits are supported as field.

To create an "indexable" or "SET" of boolean flags, the choices I have come to include:

A field of characters = 200 characters long

Maybe a field of BINARY? I could test bits using 2**N masks. I'm unsure of limits to the size of BINARY. Suspect it's the same as integer. 32 / 64 bits - maybe 128?

Is there an integer or binary field in SQL large enough to accomodate 200 bit flags?

Looking for other ideas.

Thanks,

Sam

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-21 : 08:10:57

Books Online will tell you exactly how many bytes a binary datatype will hold. Hint: it is capable of more than 128...

<O>

SamC
White Water Yakist

3467 Posts

Posted - 2002-06-21 : 08:38:28

Thanks for the response on BINARY.

So BINARY can be 8000 bytes, and SUBSTRING can be used to locate a byte. Seems this would be better than a string of characters in it's ability to set / clear a bit in a byte.

So to set / clear / check a bit - read the field, find the byte, calculate the bit position in the byte, perform the operation. Insert the byte back into the BINARY field and write back if this was a set / clear operation.

No hope that someone might have devised a more straightforward solution like Myflag(180) = 1 ?

Sam

colinm
Yak Posting Veteran

62 Posts

Posted - 2002-06-21 : 10:10:38

Have I missed something?
Can you do something like this?


DROP TABLE Rows
GO
CREATE TABLE Rows
(
ID INT NOT NULL CONSTRAINT PK_ID PRIMARY KEY,
VAL BIT NOT NULL DEFAULT 0
)


DECLARE @I INT
SET @I = 1

WHILE @I < 200
BEGIN
insert rows
select @I,0
SET @I = @I + 1
END

UPDATE Rows
SET VAL = 1
WHERE ID = 180

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-06-21 : 10:21:38

quote:

Have I missed something?
Can you do something like this?

You could, yes, but it requires an exorbitant amount of space. Your solution requires 5 bytes per bit, four for the integer and one for the bit (the remaining 7 bits in the last byte are unused). So for 180 flags you would need at least 900 bytes, and you would have to perform a table join to get at them. By contrast, a bitmapped solution requires no joins for access and takes only 23 bytes.

setBasedIsTheTruepath
<O>

SamC
White Water Yakist

3467 Posts

Posted - 2002-06-21 : 13:56:53

The suggestion of using 200 rows suggests another solution.

How about using 200 fields of type bit named B1 ... B200.

Is the overhead any better than using rows?

Sam

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-21 : 14:10:01

What's wrong with the bitmapped solution?

<O>

SamC
White Water Yakist

3467 Posts

Posted - 2002-06-23 : 12:51:57

Well, the bitmapped solution will work, but the ADO/ASP overhead to find a bit, test/set, put it back is high.

The solution using a row or a field has (almost) the elegance of referencing an array of bits in ASP:

If rs("B"&bitnum) = 1 Then
conditional statement
End If

Wouldn't it be more difficult using a bitmap?

Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources