Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Dec 30, 2004

SQL Server: Updating Integer Status Columns

By Gregory A. Larsen

In my first article 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 status.

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:

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

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:

Student

Status

Fred

late arrival, early dismissal

Barney

honor society member, member of the band, Running Start

Wilma

member of the choir, teachers aid, student mentor

Dick

member of band

Jane

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.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM