SQL Server Bitwise operators to store multiple values in one column

By:   |   Comments (13)   |   Related: > Data Types


Problem

Sometimes there may be the need to save multiple values in one column instead of creating multiple columns to store these values. Let's take for example that 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.

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 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 

-- turn on indicators 1=on 0=off
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'

Example 1

Change the values in the code above as follows to turn on Home and Office.

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

Example 1 Results

We can see the total value is 18 and it returns these two items.

18
Has Home
Has Office

Example 2

Change the values in the code above as follows to turn on Home, Office and Toll Free Fax.

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

Example 2 Results

We can see the total value is 146 and it returns these three items.

146
Has Home
Has Office
Has Toll Free Fax

Example 3

Change the values in the code above as follows to turn on all of the items.

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

Example 3 Results

We can see the total value is 254 and it returns all items.

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
  • This will work will all versions and editions of SQL Server.
  • See if this type of processing and data storage makes sense for your database
  • Check out these other bitwise operators


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Friday, March 5, 2021 - 1:54:51 PM - Tamora Back To Top (88347)
Thanks for this example! Another programmer used this method to pack availability for days of the week into a single integer field, and I used your example to figure out how to decode it.

Wednesday, July 24, 2019 - 12:22:07 AM - Divyanshu Back To Top (81846)

Hi Greg,

It is a very interesting technique, however, this would work for only binary questions. Is there a way we can use this teqnique to combine fields with data type as integer, string or date?

Thanks

Divyanshu


Friday, March 15, 2019 - 12:52:02 PM - Greg Robidoux Back To Top (79307)

Hi Joe,

I probably wouldn't use this appraoch in an application.  This is merely to show that this could be done.

Also, SQL Server seems to use this approach for many things internally.

-Greg


Friday, March 15, 2019 - 12:31:25 PM - Joe Celko Back To Top (79306)

 I think that violating normal forms is always a bad idea. In this case, you've overloaded a single column in total violation of First Normal Form (1NF). The size of bits and the complexity of them increases as the values in a given domain increase. It's also hardware dependent; is your hardware high-end, low-end or not even binary? 

https://www.red-gate.com/simple-talk/sql/t-sql-programming/bit-of-a-problem/


Monday, December 31, 2018 - 10:17:17 AM - Greg Robidoux Back To Top (78584)

Hi Philip, that's one of the downsides to using this approach.  You would need to know each of the values means in order to interpret the results.  This doesn't really save much space either, so it is not really a preferred method, but something you could do if you wanted.

-Greg


Monday, December 31, 2018 - 10:06:51 AM - Philip van Gass Back To Top (78583)

Hi Greg

I understand what you are  explaining but how does this method tell a person what the various numbers actually are ?


Tuesday, November 13, 2018 - 3:43:50 AM - Paul McMillan Back To Top (78224)

Greg

A Great Primier for this subject area

A techinque I use quite a lot and which is sometimes difficult to get across to others, who have not come across it before

I have highlighted this article to others who want an undertanding of how to use this technique

Thanks Paul


Friday, September 7, 2018 - 8:20:03 AM - Clark Hunter Back To Top (77389)

The best bitwise intro that I found, at a time when I needed it.  Thank you! 

 


Wednesday, May 18, 2016 - 10:39:19 AM - Greg Robidoux Back To Top (41508)

Hi Manoj,

if you are going to store 100 different options in one column the number will get very large and this might not be the best approach.

If you have 100 separate columns and each one could store up to 10 different options that can be done.

-Greg


Wednesday, May 18, 2016 - 2:51:46 AM - Manoj Tyagi Back To Top (41501)

 If i have 100+ categories and i want to bind the each item with 10+ categories  , is it advisable to store the Catefory ids as Binary  ?

it will increase  as  2,4,8,16................................a very Big number.  Will its be ok to have this for more than 100 categories  ?

 

 


Wednesday, July 31, 2013 - 7:35:16 AM - Max Back To Top (26068)

Thanks Greg, at last a webpage that explains bitwise logic in a way that I can understand!!! Much appreicated.

 


Tuesday, March 26, 2013 - 3:21:04 PM - Armando Prato Back To Top (23024)

Thanks, Greg!  I needed to do something like this for something I'm working on and this was invaluable!


Tuesday, January 22, 2013 - 3:48:39 AM - O Back To Top (21611)

What are the performance considerations?















get free sql tips
agree to terms