Bit-Masks

By 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 int-family 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(
       key1 tinyint,
       key2 tinyint,
       key3 smallint,
       data1 varchar(30)
)

And you populate it like this:
insert into foo values(1, 2, 3, 'hello')

You can assemble another key like this:

declare @NewKey int
select @NewKey = key1 * power(2, 24) + key2 * power(2, 16) + key3
from foo

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 (
       newkey int,
       data1 varchar(30)
)

insert into new_foo (newkey, data1)
select key1 * power(2, 24) + key2 * power(2, 16) + key3, data1
from foo

When you need to actually use the values, use something like this:

print (@NewKey & 0xFF000000) / power(2,24)
print (@NewKey & 0x00FF0000) / power(2,16)
print  @NewKey & 0x0000FFFF

OK, a couple of advanced-ish 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 bitwise-and operations and the power() function are as close to zero-overhead, zero-time 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.