Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

T-SQL Bitwise Operators in SQL Server


By:   |   Read Comments (5)   |   Related Tips: More > T-SQL

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 OT 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:



Last Update:






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.

View all my tips





More SQL Server Solutions











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    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



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

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

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

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

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

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?


Learn more about SQL Server tools