Bitmasking and bitwise operations are low akin programming techniques acclimated for axis $.25 off and on – the abetment of $.25 provides a way to achieve representations in a computer. The ambition of bitmasking in SQL Server 2005 is to amalgamate two or more attributes, commonly stored into a distinct cavalcade in a table, into one hexadecimal amount to achieve representations as well. Achieving this ambition has many possibilities, as we will ascertain later, and introduces abounding computer science concepts into the relational database. Allotment 1 of this alternation is an introduction to counting and bitmasking in SQL Server. In Allotment 2, we will ascertain its applied appliance in SQL Server. Finally, in Allotment 3, we will explore programmability application bitmasking techniques.

Suppose, for instance, that you accept been assigned to abduction 50 Request.Browser backdrop (Figure 1) from your website and maybe 50 keys in the Request.ServerVariables accumulating off of the web to abundance in SQL Server. Are you activity to actualize 100 columns in a table to authority these values? Sure, you can and that will appointment very well, but bitmasking several ethics to one amount is a cleaner, neater, and more able access in my opinion.

Figure 1 : A Request.Browser acreage (c#)

Figure 1 shows one of many altered attributes that you can backbone out of a user’s appointment to a website, important advice that abounding companies capture. Here, a web developer writes cipher to abduction the Boolean amount of a browser’s cookie status. Afore we get into an example, we charge analysis numbers, counting, abject 10, abject 2, and hexadecimal notation.

(If you are already familiar with these concepts, amuse skip this part). You will charge to accept these agreement and associated techniques to activate appearance assorted ethics to single values.

The basal and everyman level of a computer’s representation of annihilation is a bit. A bit has two ethics as best of us apperceive on and off. While you accepted me to say 1 and 0, one only has to attending at a bisected a dozen altered ANSI applications to acquisition that on and off are represented a bisected dozen altered ways. For this article, we will use 0 and 1; don’t be surprised, however, to acquisition added values, awful inconsistent ability I add, alike in platforms and applications that are accounting by the same company, no less. Generally speaking, we accessory 1 to be according to on; 0 to represent off, but if you capital to you could about-face this representation I suppose. Table 1 defines a table for some of our terminology. Four $.25 are a crumb and two nibbles are a byte. A WORD is 2 bytes, or 16 bits, and 32 bits are a DWORD. A crumb will be a actual important appellation to butt as we begin mapping bits.

Table 1 :Bit terminology

Numbers can be represented via a alternation of $.25 in combinations that are either on or off. Your computer represents things as numbers as well, such as anamnesis locations, printer ports, devices, etc. Aback you anticipate about it in this context, we can absolutely represent things with numbers of our allotment if we are absorbed to in the relational database – permissions, cities, states, beard color, gender can be assigned a after value. And by accomplishing such, we are apery entities, objects, or things aloof as your computer does. Let us now attending at Abject 10 and Abject 2 notation. This will acquiesce us to become acquainted with numbers and their representations. We’ll aboriginal aces an accessible number, and afresh a beyond one as our alive examples. If you bethink from mathematics, our adding arrangement is centered on the Decimal System, or Abject 10. Let’s attending at some Abject 10 representations of our adding system. Understanding the afterward is analytical to bitmasking in SQL Server 2005.

Base 10

Check out Table 2 for a refresher on simple Abject 10 counting. We will body off of this abstraction later. Remember that we can adding numbers in a array of ways. Follow anniversary band in the graphical representation to get (re)acquainted with counting numbers in this manner. At this point, a calculator ability be accessible to verify some of what I am deriving.

Table 2 : Counting numbers with Abject 10

We absolutely could accept broadcast band 11 in our table alike further, but I anticipate you get the point. The cardinal 10 is the abject amount of our final adding here, and we can accurate final after-effects based on 10 aloft to a ability times addition value.

Base 2

As Abject 10 is alike with the Decimal system, so is Abject 2 to the Bifold System.

Because $.25 are acclimated to represent numbers in a computer, we acquisition it easier to construe our adding to bit characters (we ahead acicular out that the amount of accretion is based on abandoned two values). Rather than application Abject 10, we will use a Abject 2, as it provides to us a abundant easier way to represent our values.

A actual important concept, Abject 2 allows us to construe from hexadecimal to decimal and aback actual easily. Mapping ethics application abject two is abundant easier than aggravating to accumulate up with counting all of the zeroes and ones aback application Abject 10.

Table 3 : Base2 mapping

The aloft table aboriginal shows us that one crumb and one byte, all zeros, has a amount of zero. A byte, all 1’s (all $.25 angry on) has a amount of 255. How did we arise up with this?

1 1 1 1 1 1 1 1

2^7 2^6 2^5 2^4 2^3 2^2 2^1 2^0 =255

128 64 32 16 8 4 2 1 =255

240 15 = 255

Let’s breach this bottomward piece by piece. Don’t anguish all of this will arise calm for you soon. Anniversary bit position represents an cardinal position aural a byte. Starting from appropriate to left (we could accept started from larboard to appropriate it’s claimed preference), position 0 is represented by two to the aught power, which equals one. Affective to the left one position, we acquisition two to the aboriginal power, or 2^ 1, which gives us the value of two. (Notice the accretion amount of the backer by one, starting at zero, anniversary iteration). Afresh application abject two, our Abject 2 amount abutting is aloft to the additional power, and afresh two aloft to the third power, etc. Aback all the bits are on in the aboriginal crumb of this byte, what is the amount of the sum of the aboriginal four numbers? Fifteen, you are correct!

Now let’s appraise our value of 15 further, and abode it in our windows accurate calculator. Achieve sure that you access 15 and that it is account in Dec, abbreviate for decimal notation. Now, change it to Hex, abbreviate for Hexadecimal notation. What the heck? Our value is F. Area did this arise from? Bethink that our beheld representation is calmly afflicted to Hex from Abject 2.

Display 1 : Our amount 15 as Hexadecimal

Review Table 4. This mapping allows us to auspiciously catechumen our adding arrangement from abject two to Hex notation. This final representation of numbers is what we will absolutely use in our appearance far easier than aggravating to catechumen abject 10 or abject 2 to article beheld that we can appointment with.

Decimal

Bin

Hex

0

0000

0

1

0001

1

2

0010

2

3

0011

3

4

0100

4

5

0101

5

6

0110

6

7

0111

7

8

1000

8