By Chris Miller on 25 August 2000 | 16 Comments | 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,


Discuss this article: 16 Comments so far. Print this Article.

If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

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

Ask a query to make reports (0 Replies)

SQL CLR, HttpWebRequest, and Connection Throttling (1 Reply)

List all dates for a Monday within a date range (6 Replies)

Urgent help needed (6 Replies)

Preview (4 Replies)

Error:Subreport could not be shown (7 Replies)

Failed to convert parameter value from a String to (3 Replies)

Export dataset into excel sheet in C# (6 Replies)

Subscribe to

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers. Articles via RSS Weblog via RSS

- Advertisement -