SQL 2005+

SQL Server Logical Bitwise Operators

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 (&):

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:

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
AND
  00011011 = 27

Exclusive OR Operator

The third operator, and the last that requires two operands, is the exclusive OR operator, often abbreviated to XOR. This operator compares the bits of the two operands in a similar manner to AND and OR. Where one bit is zero and the other is one, the resultant bit will be one. Where the two bits in the operands match, the resultant bit will be zero. For example:

  11110000 = 240
  00111100 = 60
OR
  11001100 = 204

To try the above calculation in T-SQL, use the caret symbol (^) as the operator:

You can use the XOR operator to toggle the values of individual bits in a bit field. To toggle one or more bits, XOR the field's value with the value of the bits that should be reversed. For example, to toggle the "Auto Answer Mode" flag you can use the following calculation:

  00011011 = 27
  00001000 = 8
AND
  00010011 = 19

NOT Operator

The NOT operator is a unary operator, meaning that it requires only a single operand. The operator returns the one's complement of the operand, converting all ones to zeroes and all zeroes to ones. For example:

  00001000 = 8
NOT
  11110111 = 247

To calculate the one's complement in T-SQL, the tilde symbol (~) is used as a prefix to the operand. For example, to determine the one's complement of 8, execute the following statements:

DECLARE @Value TINYINT
SET @Value = 8
PRINT ~@Value

In the example above, the value has been defined as a tiny integer, as this represents a single byte with a value between zero and 255. When using data types that can hold negative values, the results are not as easy to interpret. This is because negative numbers are represented by setting the highest order bit and using two's complement notation. To show this, run the following statement:

PRINT ~8    -- Prints -9

NB: Although the integer representation is different, the operation is correct at the bit level.

Operator Precedence

We can now add the bitwise operators to the table of precedence.