T-SQL Bitwise Operators in SQL Server

By:   |   Comments (5)   |   Related: > Data Types


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.

Create the table and populate the 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.

SQL Server T-SQL Logical OR example

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.

SQL Server T-SQL Logical AND example

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.

SQL Server T-SQL Logical XOR example

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.

SQL Server T-SQL Logical OR and NOT XOR example

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

SQL Server T-SQL Logical AND and NOT AND example

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

SQL Server T-SQL Logical XOR and NOT XOR example

T-SQL code used for this tip

--======================================================
--Create our example table
--======================================================
create table tblLogic
(
	pKey integer identity(1,1) not null primary key,
	AttributeA bit,
	AttributeB bit
)
--======================================================
--Populate to create a truth table
--======================================================
insert into tblLogic values (0,0)
insert into tblLogic values (0,1)
insert into tblLogic values (1,0)
insert into tblLogic values (1,1)

select * from tblLogic

select *, AttributeA|AttributeB as LogicalOR
from tblLogic

select *, AttributeA&AttributeB as LogicalAND
from tblLogic

select *, AttributeA^AttributeB as LogicalXOR
from tblLogic

select *, AttributeA|AttributeB as LogicalOR,
        ~(AttributeA|AttributeB) as LogicalNOT_OR
from tblLogic

select *, AttributeA&AttributeB as LogicalAND,
        ~(AttributeA&AttributeB) as LogicalNOT_AND
from tblLogic

select *, AttributeA^AttributeB as LogicalXOR,
        ~(AttributeA^AttributeB) as LogicalNOT_XOR
from tblLogic
Next Steps

The code above will also work with integer data types. Also, you can chain together the operators to perform logic operations on more than two columns.

Check out these other tips and tutorials:



sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Dallas Snider Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida and has 18+ years of SQL experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Thursday, October 29, 2015 - 1:29:20 PM - Wayne Back To Top (38993)

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.


Thursday, October 29, 2015 - 11:21:12 AM - Bill Back To Top (38989)

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.

Can anyone provide an example in the SQL Server world?


Wednesday, October 28, 2015 - 6:14:25 PM - Wayne Back To Top (38981)

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.


Wednesday, October 28, 2015 - 4:24:50 PM - Izhar Azati Back To Top (38980)

Bitwise Operators and query is more common on integer columns where you can store 8/16/32/64 bits.


Wednesday, October 28, 2015 - 11:21:11 AM - Bill Back To Top (38978)

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?















get free sql tips
agree to terms