Blog Post

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

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.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating