How many applications do you have that capture a status field for one

reason or another? Do these status fields support only a single status

value at a time? Or does the single status field support multiple different

statuses being associated with a given record at the same time? This article

will discuss how to use an integer field to support having multiple statuses assigned

to records at the same time.

An integer field is just a number,

but how is that number stored in a computer? Numbers are stored as a series of

ones and zeros, right? Remember your binary math from your Computer Science

101 class, where you learned that a byte of information is made up of 8 bits,

where each bit is either a one or a zero. Knowing this we can utilize each bit

of an integer column to represent a different status code. Therefore, a single

integer field can maintain multiple statuses at the same time for a single row,

where each status is represented by using a single bit of the integer column.

Now to better understand how to utilize the integer column for supporting

multiple statuses let me go into a little more detail.

Say I needed to have a table

where each record contains information about students. In this table, I wanted

to track the different activities that are associated with each student. The

different activities I wanted to track consist of the following different

statuses: late arrival, early dismissal, honor society member, member of the band,

member of the choir, teachers aid, student mentor, and running start, where any

given student could perform one or more of these activities or none at all.

Now to keep track of each

one of these statuses for a given student I do not want to have a different

column in my table for each one of these statuses, since most students only

perform one, or none of these activities. Therefore, I decided that I want to use

a different bit of a 4-byte integer column to represent each of these statuses.

For example, say a student had late arrival, was in the honor society, and was

a member of the band, then a binary value of 00001101 would represent the

status of this student, where the first bit tells whether they had late

arrival, the second bit identifies whether they had early dismissal, the third

bit whether they where in the honor society, the forth bit whether they where a

member of the band, and so on. When I store this binary value in my table, the

integer field will have the value 13. The following Table shows the different

integer values that represent each of the different student statuses I want to

track:

Status |
Integer Value |

Late Arrival |
1 |

Early Dismissal |
2 |

Honor Society Member |
4 |

Member of the Band |
8 |

Member of the Choir |
16 |

Teachers Aid |
32 |

Student Mentor |
64 |

Running Start |
128 |

To identify the different

integer values associated with different combinations of statuses becomes a

little mind-boggling. But rest assured there is no need for you to remember

that the integer value 13 means the student has statuses of “Late Arrival,

Honor Society Member and is a Member of the Band”, because there is a way

programmatically to handle this in a way that is easy to use and code. This easy

way is by using the OR and the AND bitwise operators. Using these operators

lets you easily set and retrieve the statuses from this single integer column.

Let’s go through an example to demonstrate. Say I have five students with the

different statuses indicated in the following matrix:

Student |
Status |

Fred |
late arrival, early |

Barney |
honor society member, member |

Wilma |
member of the choir, |

Dick |
member of band |

Jane |

As you can see, each student

has one or more statuses associated with him or her, except for Jane, who has

no status at all. So how do I populate a status column programmatically with

T-SQL code? To demonstrate how to set at integer status lets go through an

example. But first I need to create a student table that can be populated with

records, where one of the columns will be the integer “Status” column. Here is

a stripped down table definition that only has two columns, which will work for

my example:

create table Student (StudentName varchar(10), Status int)

Now that we have the table,

we need to populate the table with the first set of statuses identified in the

above matrix for Student “Fred”. The student “Fred” has a status of 1 (Late

Arrival) and 2 (Early Dismissal). To insert Fred’s record into my Student table

I use the following command:

insert into Student values ('Fred',1|2)

As you can see, I use the

notation “1|2” to populate my “Status” column. In case you are not familiar

with this logic I am using the OR bitwise operator (the vertical bar “|”). Let’s

review how this works. If you were to think of the binary representation for

the status value for “Late Arrival” (integer value 1) it would be “00000001”,

where as the binary value “00000010” represents a status of “Early Dismissal” (integer

value 2). When these two binary numbers are OR’d together you get the binary

value of “00000011” or and integer value of 3. Here are the insert statements

needed to populate all the Student records:

insert into Student values (‘Fred’,1|2)

insert into Student values (‘Barney’,4|8|128)

insert into Student values (‘Wilma’,16|32|64)

insert into Student values (‘Dick’,8)

insert into Student values (‘Jane’,0)

If I return all the records from

my Student table, the results look like this:

Student Status

——- ——

Fred 3

Barney 140

Wilma 112

Dick 8

Jane 0

As you can see, when

multiple statuses are associated with a Student it becomes hard to determine

which individual statuses are set when reviewing the value of the Status column.

To make this kind of status field work for an application there needs to be a

way to easily determine whether a particular student has an individual status

set. To do this I can use the bitwise AND operator (&). Let’s look at an

example of how this works. Here is some code that returns all the Student

records where the student has the “Member of the Band” status set, as one of

their status settings.

select * from Student where Status&8 = 8

The following records where

returned when I ran this query:

Student Status

——- ——

Barney 140

Dick 8

In the code above I used the

coding logic “StudentStatus&8 = 8” to identify the students that are

members of the band. How does this work? First, remember the integer status

value for members of the band is 8. So by using the bitwise AND operator

(&), I took the Status value and AND’d it with the value 8. Now to review

this in terms of the binary AND operation it would look like this for the

Student record where the StudentName column is set to “Barney”:

10001100

& 00001000

00001000

By doing this AND operation

only two possible values could be returned, either 0, or 8. If the value 8 is

returned from this AND operation then the Student is a member of the band,

where as if 0 is returned the student is not a member of the band.

Your application might have

the requirement to return records where more than one status is set. If you

need to search for students that have multiple statuses set, then you need to

use both the OR and the AND operators. Here is a SELECT statement that will

find all the Student records where the student is both in the band (status = 8)

and is participating in running start (status = 128):

select * from Student where Status&(8|128) = 8|128

Here I have OR’d together

the value 8 and 128. By doing the OR operation I calculated an integer value

of 136, which represents a student that is both in the band and in running

start. This value is then AND’d with the Status column to determine which Students

are members of the band and participating in the running start program. This ANDing

operation works the same as my prior example but now two different bits are

checked at the same time instead of one.

One of the advantages of

using an integer value to hold different statuses is that you can add new

statuses with out modifying your table definition. All that is needed to add

a new status is to use the next available power of 2 value for each new status.

Another advantage of using this method to store multiple statuses is that this

method takes less disk space then character data to store the same number of

statuses. A single integer column can store 31 different status values and

only takes up 4 bytes.

Storing a number of different

statuses in an integer column may seem a little strange at first, but once you

get use to it, you learn how easy it really is. This article has given you the

basic information needed to exploit an integer column for storing multiple

statuses. Next time your application needs more than just a few statuses for a

single record, you might consider using an integer column to support storing those

multiple statuses for your application.