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,


Related Articles

Using the TIME data type in SQL Server 2008 (6 March 2008)

Using the DATE data type in SQL Server 2008 (6 December 2007)

Working with Time Spans and Durations in SQL Server (15 October 2007)

DATEDIFF Function Demystified (20 March 2007)

The Cost of GUIDs as Primary Keys (8 January 2005)

Search and Replace in a TEXT column (18 January 2004)

INF: Frequently Asked Questions - SQL Server 2000 - Table Variables (7 December 2003)

How to search for date and time values (2 May 2003)

Other Recent Forum Posts

How to output data from rows in different tables into one text file with each row having different value types (17h)

SQL query (18h)

Customers with no Orders (23h)

How to write SQL query to export custom data (1d)

Merge two fields from one table with one field from another table (2d)

Regarding SQL server agent not working (4d)

How to join 2 tables with many-to-one relationship (4d)

Table normalization, I can’t remove duplicates (5d)

- Advertisement -