SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SQL Bitwise Operations

How many DB professionals have never had to deal with bitwise operations in SQL Server?  Who has never had a single value in the database represent more than one data value?  Have you ever had one of these fields serve as the(implicit or explicit) foreign key to a source table?

Sometimes it can seem a bit tricky dealing with such data – especially the last.  I’m not going to delve into the complexity of such scenarios.  I do want to present a basic intro however.

There are three basic operators for bitwise operations.  The operators are &, |, and ^.  These operators perform logical operations against integer type data.  Here is an example of what each would return when using the same values:

SELECT 175&75 AS BitAnd, 175|75 AS BitOr, 175^75 AS BitXOr
BitAnd	BitOr	BitXOr
11	239	228

When performing a Bit & operation, the bits in the result are set to 1 if and only if both bits (for the current bit being resolved) in the input expressions have a value of 1.  Any other combination results in a 0.

When performing a Bit | operation, the bits in the result are set to 1 if either of the corresponding bits is a 1.  A 0 is returned when both values are 0 in the corresponding bit.

When performing a Bit ^ operation, the bits in the result are set to a value of 1 if 1 of the two bits (but not both) has a value of 1.  Any other combination (either all 0s or all 1s) will result in a value of 0.

As I work with this a bit, I will be sharing a particular scenario that has been quite interesting for me.


Posted by Joe Celko on 12 March 2011

I would not do this at all. It violates First Normal Form, and gives you code that is totally dependent on the hardware used (highend, low end, one or twos complement, word size, etc). The goal of SQL is a high level, abstract data base language, not a return to assembly language programming.

Posted by andreas.jansson on 14 March 2011

Joe Celko: Mr Brimhalls rhetorical questions indicate that he is fully aware of this opinion. Yet the practicality of storing multiple status choices in single chunks of integer data can sometimes be so overwhelming that one can argue in favour of "breaking the rules". I for sure sometimes resort to this way of storing data. A data field for choices made, accompanied by a "list" containing the individual choices in clear text, and their own separate values (1, 2, 4, etc).

Posted by Andy P on 15 March 2011

Yikes! If you absolutely have to deal with this kind of data in a third-party database, then bitwise operations are the way to go. Just occasionally you MIGHT find an application where storage space is so critical that you can justify this kind of denormalisation.

But for most people, most of the time, bitwise operations just don't come up - if you have multiple values in different bits of the same column, you have a major design flaw.

I'm not having a go at your post by the way, Jason. If people come across these horrors, they need to know how to deal with them. I just want to emphasise Joe Celko's point that no-one should be designing relational databases like this; if you come across one, you should consider it a very bad smell!

Leave a Comment

Please register or log in to leave a comment.