# 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.

## 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.

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

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

 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?