BitMasksBy Chris Miller on 25 August 2000  Tags: Data Types 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.
Take a binary or intfamily datatype (smallint, varbinary, int, binary, whatever). Basically, it's just a collection of bits, in the case of an int there are 32 bits, smallint is 16 bits, and tinyint is 8 bits.
OK, now, let's say you've got a table like this: create table foo( And you populate it like this: insert into foo values(1, 2, 3, 'hello') You can assemble another key like this: declare @NewKey int That will assemble the key for you. The variable @NewKey then would contain a basic concatenation of the values that make up the key. If you wanted to do that as an insert statement: create table new_foo ( When you need to actually use the values, use something like this: print (@NewKey & 0xFF000000) / power(2,24) OK, a couple of advancedish things here. First, the ampersand (&) is performing a bitwise and operation between the value in @NewKey and a hexidecimal value. Second, the hexadecimal value, which starts out 0x. You can always do integers in hexadecimal just by prefixing the number with 0x. So, when we do the first bitwise and, we're getting the value of the first two hexadecimal digits of @NewKey. Two hex digits is 1 byte. Problem is that the two hexadecimal digits are shifted to the left by 24 places, so we need to divide them back down to what we put in. Now, why the heck would you want to do all this? Good question. Basically, keep in mind that the bitwiseand operations and the power() function are as close to zerooverhead, zerotime functions as you can get. So we're not adding much overhead, but we are making it easier to index and this will possibly maybe end up with a smaller, faster index. Good luck, rocketscientist.

 Advertisement  