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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

 SQL Server Forums Profile | Active Topics | Members | Search | Forum FAQ Register Now and get your question answered!
 All Forums  Site Related Forums  Article Discussion  Article: Bit-Masks Reply to Topic  Printer Friendly
Author  Topic

USA
0 Posts

 Posted - 08/09/2000 :  10:44:43 chris writes "I am having difficultly finding information on bit masks. Can you help me understand advantages and how to create using VID6.0? SQL Server 7.0/Win98" Bitmasks are interesting. Here's the scoop.Article Link.

jfuex
Starting Member

USA
29 Posts

tymberwyld
Starting Member

4 Posts

 Posted - 10/11/2004 :  15:30:07 Ok, you mentioned using an "Int" DataType field (lets assume this is SQL Server). I've tried doing this, and maybe I don't understand enough, but I cannot store 32bits into an Int Column.Lets say I have an Enum and the values are 1 - 23. Lets also assume that each value is represented by 1 bit (Booleans). Using your example, I should be able to do something like this:Set @USER_RIGHTS = Power(2,32) -- Set the 32nd BitI get Arithmetic Overflow Errors doing this. What am I doing wrong and how to I get this working??

robvolk
Most Valuable Yak

USA
15732 Posts

 Posted - 10/11/2004 :  22:18:13 The 32nd bit of a 32 bit word is for flagging negative/positive values. You can set it by negating the numeric value:SET myIntColumn = -myIntColumnAlso, because the upper limit of an int is 2^31 - 1, you cannot raise 2 to any power greater than 30. You can get around this with:SET myIntColumn=Power(2, 30) - 1 + Power(2, 30)I leave the bitwise math equivalent of that to you (I sure can't figure it out) Don't forget that you can do bitwise operations using binary or varbinary values too (as the article demonstrates), as long as one of the arguments is an int, smallint, or tinyint. You can then convert the binary value to an int, or just store is as binary. I'd suggest that if you had more than 32 settings you want to store, use a regular binary value instead. You can do bitwise operations on up to 4 bytes of it at a time, and if you need to work with more than 4 bytes you can use SUBSTRING() to extract the extra bytes you need:DECLARE @flags binary(8)set @flags=0xFFEEDDCCBBAA9900select substring(@flags, 1, 4) & 16384select substring(@flags, 5, 4) & 4096Keep in mind that SQL Server is not designed for efficient bitwise handling, and unless you need to save many dozen or hundreds of settings (especially if you have to query them regularly) you are most likely better off storing them in individual bit columns. In SQL Server 2000 bit columns can be indexed. Additionally, you can create calculated columns that do the bitwise operations needed to mask out a setting, making it easier to read and query, and these can also be indexed in SQL Server 2000.

tymberwyld
Starting Member

4 Posts

 Posted - 10/11/2004 :  22:41:48 What I ended up doing (after further reading and understand) is making a Column called "SecurityAccess" with a DataType of BigInt. At this point in time I only need 23 values (or Bits) but I figured to go with the 64-Bit handling of a BigInt. Please let me know if there is a better way, I'll look into VarBinary DataTypes.However, this made the Querying of what I need faster than ever! I was impressed! For instance, if I need all the Managers in the App:Select * From Users Where SecurityAccess & Power(2, 4) > 0There it is, short and sweet!

robvolk
Most Valuable Yak

USA
15732 Posts

 Posted - 10/11/2004 :  22:44:42 Take a shot at creating a calculated column and indexing it, you may see an even bigger improvement.

tymberwyld
Starting Member

4 Posts

 Posted - 10/11/2004 :  22:48:21 Would you happen to have a sample Script? I'm not too familiar with Calculated Columns...and I'm not sure what it would be calculating. I don't have a Bit Column for each setting because the Settings can grow / shrink over the course of the App's lifetime...so I didn't want to "hard-code" Columns or have to Create / Delete Columns everytime a setting was Added / Deleted. In the begining I thought about Child Tables to store the info, but that would be insane to maintain because I need this info in 3 different tables.

robvolk
Most Valuable Yak

USA
15732 Posts

 Posted - 10/11/2004 :  22:59:47 ALTER TABLE Users ADD Level4 AS SIGN(SecurityAccess & 16) -- gives a 1 or 0 if 4th bit is setCREATE INDEX idx_Users_Level4 ON Users(Level4) -- index columnSELECT * FROM Users WHERE Level4=1 -- regular queryForgot to mention that using this method to index the computed column lets the query optimizer choose an index seek instead of a table scan, which can greatly improve the query performance. Even if you indexed the SecurityAccess column, since you were originally doing a bitwise operation in the WHERE clause:WHERE SecurityAccess & Power(2, 4) > 0That index can't be used, because the bitwise expression is to the left of the greater-than operator. Nor could this expression be written in a different way. This is known as a non-SARGable expression (SARG = search argument). A SARGable expression places a column value alone on the left side of a comparison operator:WHERE Level4=1The optimizer can then look for an index on that column and use it to evaluate the query.

coolerbob
Aged Yak Warrior

United Kingdom
841 Posts

coolerbob
Aged Yak Warrior

United Kingdom
841 Posts

 Posted - 05/17/2007 :  12:24:42 If I have an int column as I decribed in my previous post, and I want to unset (set to zero/off) one of the bits, how do I do that?Setting it on is easy. update MyTableset MyCol=MyCol| 2In the above example, this is the result if 8 was in the MyCol: 1000 OR 0010 = 1010 (8 or 2 = 10)But if I wanted to switch one bit off, e.g. turn 1010 back into 1000, how do you do that?I can test wether bit #1 is on or not because (using MyCol & 2):1101 AND 0010 = 0000 1111 AND 0010 = 0010 But how do I in SQL turn a bit off if it's on? How do I turn 10 into 8, 6 into 4, 14 into 12, 15 into 13, 11 into 9, etc...It looks like you just subtract two if the Bitwise AND operation is non-zero. Is that right?

jsmith8858
Dr. Cross Join

USA
7423 Posts

 Posted - 05/17/2007 :  13:11:25 if you have 0110and you want to "unset" the second bit (from the right), you just AND it with:1101which gives you:0100- Jeffhttp://weblogs.sqlteam.com/JeffS

jsmith8858
Dr. Cross Join

USA
7423 Posts

 Posted - 05/17/2007 :  13:12:28 Let me add: I have no idea why anyone would store data as bits in an INT like this in T-SQL. Why would you want to do this?- Jeffhttp://weblogs.sqlteam.com/JeffS

spirit1
Cybernetic Yak Master

Slovenia
11752 Posts

 Posted - 05/17/2007 :  14:04:40 flags maybe?_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp

Kristen
Test

United Kingdom
22859 Posts

 Posted - 05/17/2007 :  14:06:35 I've had situations where I wanted bit-fields of "properties" that I wanted to match on.Dunno if it was efficient or not, but that's how we did it!Kristen

coolerbob
Aged Yak Warrior

United Kingdom
841 Posts

 Posted - 05/18/2007 :  04:22:34 quote:Originally posted by jsmith8858Let me add: I have no idea why anyone would store data as bits in an INT like this in T-SQL. Why would you want to do this?- Jeffhttp://weblogs.sqlteam.com/JeffSPerformance boost. Currently I have to do some joins and where clauses to establish this information. Picking up on column from one table is a whole lot faster. I'm now at the performance tuning part of this particular project. The old database before I arrived was badly de-normalized. I came in and made a normalized one and converted the denormalized data from the old db into the new. They've been running on the new db for owver a year now and all is well. Now we are adding some denormalized bits to add performance gains - while of course leaving the normalized original source data in place. Edited by - coolerbob on 05/18/2007 04:32:50

coolerbob
Aged Yak Warrior

United Kingdom
841 Posts

 Posted - 05/18/2007 :  04:27:57 quote:Originally posted by jsmith8858if you have 0110and you want to "unset" the second bit (from the right), you just AND it with:1101which gives you:0100- Jeffhttp://weblogs.sqlteam.com/JeffSThanks! update MyTableset MyCol=MyCol & 14I was being a little thick there... Edited by - coolerbob on 05/18/2007 04:28:28

coolerbob
Aged Yak Warrior

United Kingdom
841 Posts

 Posted - 08/10/2007 :  08:23:14 Some of you may have a use for grey codes too.You can read more about that here:http://en.wikipedia.org/wiki/Grey_code
Topic
 Reply to Topic  Printer Friendly Jump To: Select Forum General SQL Server Forums       New to SQL Server Programming       New to SQL Server Administration       Script Library       Data Corruption Issues       Database Design and Application Architecture SQL Server 2012 Forums       Transact-SQL (2012)       SQL Server Administration (2012)       SSIS and Import/Export (2012)       Analysis Server and Reporting Services (2012)       Replication (2012)       Availability Groups and DR (2012)       Other SQL Server 2012 Topics SQL Server 2008 Forums       Transact-SQL (2008)       SQL Server Administration (2008)       SSIS and Import/Export (2008)       High Availability (2008)       Replication (2008)       Analysis Server and Reporting Services (2008)       Other SQL Server 2008 Topics SQL Server 2005 Forums       Transact-SQL (2005)       SQL Server Administration (2005)       .NET Inside SQL Server (2005)       SSIS and Import/Export (2005)       Service Broker (2005)       Replication (2005)       High Availability (2005)       Analysis Server and Reporting Services (2005)       Express Edition and Compact Edition (2005)       Other SQL Server Topics (2005) SQL Server 2000 Forums       SQL Server Development (2000)       SQL Server Administration (2000)       Import/Export (DTS) and Replication (2000)       Transact-SQL (2000)       Analysis Services (2000)       MSDE (2000) Development Tools       ASP.NET       Reporting Services Development       Other Development Tools Site Related Forums       Site Related Discussions       Article Discussion       Poll Discussion       The Yak Corral Other Forums       SQL Server 6.5 \ SQL Server 7.0       Other Topics       MS Access       ClearTrace Support Forum Old Forums       CLOSED - General SQL Server       CLOSED - SQL Server 2005/Yukon  -------------------- Home Active Topics Frequently Asked Questions Member Information Search Page
 SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC