T-SQL Bitwise Operators in SQL Server

Problem

I need to perform logical operations on my SQL Server database columns. How can I do this in T-SQL?

Solution

T-SQL provides bitwise operators to perform logical operations on
SQL Server database table columns. In this tip, we will examine the OR, AND and XOR bitwise operators. We will begin by creating a table with a primary key column, along with two columns of the BIT datatype. Next, we will populate the table with four rows as if we were creating a classic truth table.

Pipe Character (|)
is the Bitwise OR Operator in T-SQL

The pipe character (|) is the bitwise OR operator.

The query below produces the truth table for OR operations between the AttributeA and AttributeB columns.
The LogicalOR will be 1 if any either AttributeA or AttributeB equals 1.

Ampersand character (&) is the Bitwise AND Operator in T-SQL

The ampersand character (&) is the bitwise AND operator.

The following query produces the truth table for AND operations between the AttributeA and AttributeB columns.
The LogicalAND will be 1 if both AttributeA and AttributeB equals 1.

Caret character (^) is the Bitwise Exclusive OR Operator in T-SQL

The caret character (^) is the bitwise XOR (exclusive OR) operator.

The T-SQL code below produces the truth table for XOR operations between the AttributeA and AttributeB columns.
The LogicalXOR will be 1 if AttributeA does not equal AttributeB.

Tilde character (~) is the Bitwise NOT Operator in T-SQL

The tilde character (~) is the bitwise NOT operator.

The example below shows the OR operation above along with the negation of the OR operation.

The image below shows the AND operation above along with the negation of the AND operation.

The T-SQL below shows the XOR operation above along with the negation of the XOR operation.

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter
I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.

I heard of an example in the early days of data warehousing where instead of storing the state that an address was in, that it was more efficient from a search strategy to employ 50 bit fields to represent state. But I know nothing about DW, I have no idea if this is still considered a reasonable approach with column data stores and such.

Expanding on my earlier comment, I 'd like to see a real world example where "bit fiddling" makes a performance difference today. Sure, 20+ years ago, when storage, CPU and memory was constrained, such operations added value.

Can it be demonstrated this is still the case? Manipulating the bits in an integer goes against everything I've learned about data modeling. However, if theres a significant performance gain then the trade-off would be worth it.

I would really like to see an example of using bitwise operators against an integer. The very first database that I worked on in the mid '80s had the original code written in Algol or something, and rather than store a negative sign, they turned on the third bit of the last byte of a number, changing the last character from a number to a letter. I didn't understand bitwise operators against integers then, not that I claim any expertise now. But you never know when you might need it. T-SQL triggers being one area where they're not uncommon.

Good article. I learned about bitwise operators in my college programming classes and have rearely seen them used sense.

In the relational database world, can you provide an example where using bit data types and bitwise operators dramatically improves the efficienty of a database query?

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.