SQL Server: Updating Integer Status Columns

In my first
in this series, I discussed how to store different statuses in a
single integer column. In this article, I will expand on the topic of using
integer columns for storing statuses and will discuss how to update the integer
status column to change or remove any given status.

Prior to getting into how to update a status column, let us
first review how an integer column actually stores multiple statuses. The
numerical value of an integer column can be represented a number of ways. One
of those ways is to represent an integer as a series of 1’s and 0’s; I refer to
this as the binary format of an integer. An integer is 4 bytes long, and each
byte can contain up to 8 bits. Therefore, a single integer column can
represent 31 different status values. Let us look at a simple example. Say an
integer column contains the integer value 140. The binary representation of
this number is 0000000000000000000000010001100. This binary number has 3 bits
that are set to 1, and 28 bits that are set to 0. Each bit that is set to 1
indicates that the status associated with that bit is set to TRUE, where as if
the bit is set to 0 then the status for that bit is set to FALSE. Each one of
the 31 different bits of the above binary value would represent a different

Now that we know how an integer value can store different
statuses using a binary string of 1’s and 0’s, let us look at an actual table
that will store multiple records, where each record will contain an integer
status column. I am going to use the same table and statuses as in my first
article. Here is the code to create my table:

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

Here is a matrix that represents all the possible statuses
for a given student:


Integer Value

Late Arrival


Early Dismissal


Honor Society Member


Member of the Band


Member of the Choir


Teachers Aid


Student Mentor


Running Start


I will need to populate my Student table with some records
so I can use these records later on to perform updates. The matrix below shows
the different statuses for each student record that I will be inserting into my
Student table:




late arrival, early dismissal


honor society member, member of the band,
Running Start


member of the choir, teachers aid, student


member of band


Here is the code I use to add these 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)

Now that I have my "Student" table populated, we
can start looking at how to update statuses.

Gregory Larsen
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles