SQL Server Bitwise operators store multiple values in one column

By: Greg Robidoux   |   Read Comments (2)   |   Related Tips: More > T-SQL

Problem
Sometimes there may be the need to save multiple values into one column instead of creating multiple columns to store these indicators.  Let's take for example we need to know who has different telephone types and instead of having multiple indicator columns for each type of telephone number you use one column to store the different values.  So for example value 1 = home phone, 2 = mobile phone, 3 = fax, etc...  You could have three different bit (yes/no) columns such as homeNumberIndicator, mobileNumberIndicator and faxNumberIndicator, but each time you add a new phone type you need to add a new column to your table.  Another way to do this is to store the data in a binary format and use the bitwise (&) operator that SQL Server offers to store all of the data in one column and to also easily tell what values are applicable based on the value stored.

Solution
Basically what we want to do is store one value for different types of phone numbers that a person has in one column instead of having multiple columns for each different type.  This is just one purpose for doing this, but there are countless other reasons you may want to store data this way. So in order to do this we need to save the data in a binary format, so we can then use the bitwise (&) operator to retrieve the data.  So the first thing we need to do is figure out the values we want to store and then use a binary representation for each of these values.  Here is the representation of the data for each of the different phone types we will store.

• 2 = home phone number
• 4 = home fax number
• 8 = mobile phone number
• 16 = office phone number
• 32 = office fax number
• 64 = toll free office number
• 128 = toll free office fax number

To better understand how this will work, let's say someone has both a home number and an office number.  The value that would be stored in the data column would be 18 (2 home + 16 office).  Another example could be home number and home fax number which would be 6 (2 home + 4 home fax).

The use of the bitwise operator comes in play when updating and reading data from this column.  What the bitwise operator allows you to do is to compare two different values at a binary level and tell you whether the two numbers intersect. 

Here is a simple example of this operator at work.  We are doing a simple comparison of the first value against the second value.  Since the numbers are based on binary representation, the numbers increase as follows: 1,2,4,8,16, etc...  So for these numbers we want to see where value one intersects with value two.  Here are the examples:

PRINT 1 & 0 --(=0) PRINT 1 & 1 --(=1) PRINT 1 & 2 --(=0) PRINT 1 & 3 --(=1) PRINT 1 & 4 --(=0) PRINT 1 & 5 --(=1)

• For line 1, 1 is not in 0 (0 = 0), so this returns 0.
• For line 2, 1 is in 1 (0,1 = 1), so this returns 1.
• For line 3, 1 is not in 2 (0,2 = 2), so this returns 0.
• For line 4, 1 is in 3 (0,1,2 = 3), so this returns 1.
• For line 5, 1 is not in 4 (0,4 = 4), so this returns 0.
• For line 6, 1 is in 5 (0,1,4 = 5), so this returns 1.

To take this a step further and create a representation for our telephone number problem, the following code is a simple set of code that sets the indicator either on=1 or off=0 for each of the components of the phone number indicator.  In addition, to setting the indicators for each value to either 1 or 0, we are also using the power indicator to allow us to get the binary representation easier. The first few lines set the values and prints the binary representation and the second set prints out each line that is true based on the binary representation.

DECLARE @phoneIndicator INT DECLARE @home bit,         @homeFax bit,        @mobile bit,         @office bit,         @officeFax bit,        @tollfreeOffice bit,         @tollfreeFax bit SET @home = 0 SET @homeFax = 0   SET @mobile = 0    SET @office = 0    SET @officeFax = 0         SET @tollfreeOffice = 0  SET @tollfreeFax = 0       SET @phoneIndicator = POWER(2*@home,1)                         + POWER(2*@homeFax,2)                        + POWER(2*@mobile,3)                        + POWER(2*@office,4)                         + POWER(2*@officeFax,5)                         + POWER(2*@tollfreeOffice,6)                         + POWER(2*@tollfreeFax,7) PRINT @phoneIndicator IF ( (2 & @phoneIndicator) = 2 ) PRINT 'Has Home' IF ( (4 & @phoneIndicator) = 4 ) PRINT 'Has Home Fax' IF ( (8 & @phoneIndicator) = 8 ) PRINT 'Has Mobile' IF ( (16 & @phoneIndicator) = 16 ) PRINT 'Has Office' IF ( (32 & @phoneIndicator) = 32 ) PRINT 'Has Office Fax' IF ( (64 & @phoneIndicator) = 64 ) PRINT 'Has Toll Free Office' IF ( (128 & @phoneIndicator) = 128 ) PRINT 'Has Toll Free Fax'

Here are some example runs:

Example 1

SET @home = 1 SET @homeFax = 0   SET @mobile = 0    SET @office = 1    SET @officeFax = 0         SET @tollfreeOffice = 0  SET @tollfreeFax = 0

Example 1 Results

18 Has Home Has Office

Example 2

SET @home = 1 SET @homeFax = 0   SET @mobile = 0    SET @office = 1    SET @officeFax = 0         SET @tollfreeOffice = 0  SET @tollfreeFax = 1

Example 2 Results

146 Has Home Has Office Has Toll Free Fax

Example 3

SET @home = 1 SET @homeFax = 1   SET @mobile = 1    SET @office = 1    SET @officeFax = 1         SET @tollfreeOffice = 1  SET @tollfreeFax = 1

Example 3 Results

254 Has Home Has Home Fax Has Mobile Has Office Has Office Fax Has Toll Free Office Has Toll Free Fax

As you can see the use of the bitwise (&) operator allows us to compare the values to see if there are any intersecting values in the number.  This may not be a perfect solution to your data storage needs, but it does give you another option for storing and retrieving your data.

Next Steps

• See if this type of processing and data storage makes sense for your database
• Check out these other bitwise operators

Last Update: 4/9/2007

About the author

Greg is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips

We Recommend

• More Database Developer Tips...

Comments and Feedback:

Tuesday, January 22, 2013 - 3:48:39 AM - O	Read The Tip
What are the performance considerations?

Tuesday, March 26, 2013 - 3:21:04 PM - Armando Prato	Read The Tip
Thanks, Greg!  I needed to do something like this for something I'm working on and this was invaluable!

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates Signup for our newsletter

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

SQL Server having some performance issues? Idera SQL check. FREE SQL Server enhancement. SQL Monitor: prioritize your SQL Server workload with easy-to-use performance monitoring What grade do you think your SQL Servers get? Find out with Edgewood's Health Check consulting services. Secure column & whole database on all versions and editions of SQL Server with NetLib’s TDE Demystify TempDB Performance and Manageability