SQL 2005+

SQL Server Logical Bitwise Operators

by Richard Carr, published at http://www.blackwasp.co.uk/SQLBitwiseOperators.aspx

The thirty-fifth part of the SQL Server Programming Fundamentals tutorial investigates the logical bitwise operators. These operators perform operations upon the individual binary digits that are held within numeric or binary data types.

Binary Logic and Bit Fields

This article continues a series examining the Transact-SQL (T-SQL) operators. In this article we will be examining the logical bitwise operators. These operators allow you to work with the individual bits of binary data held within integer and binary data types. Bitwise operations are often used to read or modify single bits within a bit field. A bit field can be thought of as a series of individual bits held within an integer value. Each bit represents a single option that can be "on" or "off". Consider the following table, which describes the status flags for a modem.

Were you to read the status of the modem, it would be returned in an integer value. If the status value were 27, or 00011011 in binary, this would tell us that the modem is off-line because the carrier signal is absent. It would also indicate that the modem is set to log data, is in auto answer mode, is not echoing commands and is using eight data bits with odd parity.

Bitwise logic is perfectly suited to working with bit fields, with operators that can easily be used to read, set and clear individual bits. This use is somewhat rare in SQL Server databases, as flags are generally stored in columns of the "bit" data type. However, it is important to understand the bitwise operators if you will be integrating with other systems that provide data in this manner.

Bitwise Operators

Previous articles have included examples that can be executed against the JoBS tutorial database. In this article we will use simple examples that do not require any database to work against. This is because the JoBS database does not include any information held in bit fields or any other form that would require bitwise operations.

AND Operator

The first of the bitwise operators is the logical AND operator. This operator compares the bits of two operands. Where both bits at a matching location in the values are set, the bit in the resultant value will be set. All other bits in the result will be set to zero. For example, consider the columns of bits in the following AND calculation:

  11110000 = 240
  00111100 = 60
AND
  00110000 = 48

You can perform this calculation using T-SQL's AND operator, which uses the ampersand symbol (&):

PRINT 240 & 60

The AND operator is useful in bit fields when you wish to check if a specific flag is set. To do so, you can use the operator to AND the bit field value with the value of the single bit to be tested. If the result is zero, the bit is not set. If it is not zero, the bit is set. For example, to test the value of the "Auto Answer Mode" flag in the modem example you can use the following calculation:

  00011011 = 27
  00001000 = 8
AND
  00001000 = 8  -- Non-zero so bit is set

You can also use the AND operator to clear bits. To clear one or more bits from a bit field, AND its value with the one's complement of the bits to be zeroed. This is the value that has the opposite value in every bit. For example, to clear the "Auto Answer Mode" flag you can use the following:

  00011011 = 27
  11110111 = 247
AND
  00010011 = 19

NB: This operation ensures that a bit is clear. If the bit in the original value is already zero, it will remain zero.

OR Operator

The logical OR operator is similar to AND as it works with two values. The difference is that where either of the bits at a specific location is set, the resultant bit will also be set. If both operands contain a zero at a position, the resultant bit will be clear. For example:

  11110000 = 240
  00111100 = 60
OR
  11111100 = 252

To try this calculation in T-SQL, use the vertical bar symbol (|) as the operator:

PRINT 240 | 60

You can use the OR operator to ensure that individual bits in a bit field are set. To set one or more bits, OR the field's value with the value of the bits that should have values of one. For example, to ensure that the "Auto Answer Mode" flag is set you can use the following operation:

  00010011 = 19
  00001000 = 8
OR
  00011011 = 27