Bitwise Functions (Transact-SQL)
These functions perform bitwise operations on integer values.
& (Bitwise AND)
expression1 & expression2
Performs a bitwise AND operation between two integer expressions.
The result is the value of the bits that are set (1) in both input expressions.
Parameters
expression1: An integer expression.expression2: An integer expression.
Return Value
- Returns the result of the bitwise AND operation.
Note: For the bitwise AND operator, the input values are treated as binary numbers.
Each bit of the first operand is compared with the corresponding bit of the second operand.
If both bits are 1, the corresponding result bit is set to 1. Otherwise, the corresponding result bit is set to 0.
-- Example:
SELECT 10 & 7; -- Binary: 1010 & 0111 = 0010 (Decimal: 2)| (Bitwise OR)
expression1 | expression2
Performs a bitwise OR operation between two integer expressions.
The result is the value of the bits that are set (1) in either of the input expressions.
Parameters
expression1: An integer expression.expression2: An integer expression.
Return Value
- Returns the result of the bitwise OR operation.
Note: For the bitwise OR operator, the input values are treated as binary numbers.
Each bit of the first operand is compared with the corresponding bit of the second operand.
If either bit is 1, the corresponding result bit is set to 1. Otherwise, the corresponding result bit is set to 0.
-- Example:
SELECT 10 | 7; -- Binary: 1010 | 0111 = 1111 (Decimal: 15)^ (Bitwise XOR)
expression1 ^ expression2
Performs a bitwise exclusive OR (XOR) operation between two integer expressions.
The result is the value of the bits that are set (1) in one of the input expressions, but not both.
Parameters
expression1: An integer expression.expression2: An integer expression.
Return Value
- Returns the result of the bitwise XOR operation.
Note: For the bitwise XOR operator, the input values are treated as binary numbers.
Each bit of the first operand is compared with the corresponding bit of the second operand.
If the bits are different (one is 0 and the other is 1), the corresponding result bit is set to 1.
If the bits are the same (both 0 or both 1), the corresponding result bit is set to 0.
-- Example:
SELECT 10 ^ 7; -- Binary: 1010 ^ 0111 = 1101 (Decimal: 13)~ (Bitwise NOT)
~ expression
Performs a bitwise NOT operation on an integer expression.
This operation inverts all the bits of the input expression.
Parameters
expression: An integer expression.
Return Value
- Returns the result of the bitwise NOT operation.
Note: The bitwise NOT operator flips each bit. A 0 becomes a 1, and a 1 becomes a 0.
The exact result depends on the size of the integer data type. For signed integers, this also affects the sign bit.
-- Example (assuming a small integer representation for simplicity):
-- If 10 is represented as ...00001010
SELECT ~10; -- Result would be ...11110101
-- In SQL Server, for a signed INT, this is usually represented as -11.
-- The two's complement representation is complex for a simple inline example.
Common Use Cases
Bitwise functions are often used for:
- Flag Management: Storing multiple boolean states within a single integer column.
- Efficient Data Packing: Representing sets of options or permissions compactly.
- Low-Level Data Manipulation: Working with binary data or specific bit patterns.
Considerations
- Bitwise operations are performed on integer data types. Ensure your expressions evaluate to integer types.
- Be mindful of the size and signedness of the integer data types when performing bitwise NOT operations.
- Understand the binary representation of your numbers to predict the outcome of these operations.