June 10, 2002 at 12:30 pm
In a table that has a column that contains a bitmask, I'm looking for a method to determine all the related bits that are set without first breaking the value out into the included bits and doing separate queries on each or dynamically creating the query based upon the set bits.
Example:
CREATE TABLE [dbo].[PersonTypes] (
[PersonTypeBit] [int] NOT NULL ,
[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
In a related table, I can easily find all records where one or more bits are set by using:
"Select * from tablename where persontypebit & 1 = 1", where I query for any 1 or more bits based upon value.
However, if I am going the other way, and want to see all of the matching attributes in the PersonTypes table for a specific value, is there a way to do this in a single query?
Example:
I have a record from the Persons Table and the PersonTypeBit column is 13, which means that bits 0, 2 and 3 are set and I want to return the corresponding descriptions for those attributes.
Instead of checking EACH bit to see if it is set and then doing:
"Select * from persontypes where persontypebit & 1 = 1 and persontypebit & 4 = 4 and persontypebit & 8 = 8"
Or - is there a better method to implement the use of a bitmask field?
June 10, 2002 at 1:21 pm
You didn't say if persontypebin & 2 = 2 matters or not. If it doesn't, you can do:
SELECT *
FROM persontypes
WHERE persontypebit & 13 = 13
[/code]
If it needs to be off, then do the following:
SELECT *
FROM persontypes
WHERE persontypebit & 15 = 13
[/code]
When we query against sysconstraints, status is a bit field. The issue there is 001 = Primary Key, 011 = Foreign Key, and 101 = Default. As a result, if we only want Primary Keys, we have to a status & 7 = 1.
K. Brian Kelley
bkelley@sqlservercentral.com
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
June 10, 2002 at 1:37 pm
Thank you for the reply, but I think I'm missing something.
Let me provide a bit more information-
In the example, the PersonTypes table has four records
PersonTypeBit Desription
1 Customer
2 Employee
4 Vendor
8 Client
If a related Person record has the a value of 13 for the PersonTypeBits field, it would indicate that bits 0, 2 and 3 were set (1+4+8). I am looking for a query that would return the three records for 1 - Customer, 4 Vendor and 8 Client.
If I use the example you suggested,
SELECT * FROM persontypes WHERE persontypebit & 15 = 13
no records are returned.
June 10, 2002 at 2:49 pm
Ok, I think I got it using an Inner Join and a bitwise OR '|'
SELECT * FROM persontypes Inner Join Persons on Persons.PersonTypeBits = (PersonTypes.PersonTypeBit | Persons.PersonTypeBits)
where PersonID = [whatever value]
Is this methodology common (bitmask value columns)?
Using redundant data seems to be much more prevalent - storing a record in a separate table for each attribute, vs AND'ing the bits (attributes) into a single value which is then stored with the parent object.
I would be interested in others experiences in this.
June 10, 2002 at 3:17 pm
I try not to use it, since in terms of space there isnt much difference and whether using separate bit columns or one consolidated column an index isnt likely to be useful. Overall I find separate cols easier for both me and the developers to work with.
Andy
June 10, 2002 at 8:13 pm
I use it often and never have issues. Mostly what I use it for is representing 15 minute increments of an hour in a scheduling database. I wanted 24 columns instead of 96 which made it awfull to read. You do not get any space savings except the name storage of the columns in systables. I will look at this tomorrow and see if I can give you any better help. I think I would have written this way personally.
SELECT * FROM persontypes Inner Join Persons on PersonTypes.PersonTypeBit & Persons.PersonTypeBits = Persons.PersonTypeBits
WHERE PersonID = [whatever value]
But this would be similar to the bit is 1 and 4 and 8 and includes any that are including 2. If you want to eliminate 2
it may take some work.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Edited by - antares686 on 06/11/2002 05:09:40 AM
June 11, 2002 at 7:43 am
Thank you for the replies.
The bitwise OR with the Inner Join returns only those rows with the included bits selected, so I have resolved that part of the issue.
Programatically, having a bitmask column fits more logically with the object model and require no modifications to the table as additional attributes were added.
In researching this issue, I haven't found much on this topic. What little I have found, reflecting the multiple columns (one per bit/attribute) or redundant data model - using a separate table and a row for each included attribute per record.
While I agree that differences in space usage is negligible and since queries related to this issue would be singular and as such also not an issue, maintainability and extensibility become the driving factors. As such, I would like to know of any reasons why NOT to utilize a bit mask column.
June 11, 2002 at 8:30 am
Well...there must be some!
Using a bitmap vs separate bit fields would make it hard to change later to a multi state indicator - pretty common to start out with yes/no, later to have support 'dont know'. With separate fields you could just alter to a tinyint.
Another very related issue might be that it doesn't support nulls - so you're locked into yes/no.
Andy
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply