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:
[codesyntax lang=”tsql”]
select 175&75 as BitAnd, 175|75 as BitOr, 175^75 as BitXOr
[/codesyntax]
[codesyntax lang=”tsql”]
BitAndBitOrBitXOr 11239228
[/codesyntax]
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.
