& (Bitwise AND) (Transact-SQL)
Applies To: SQL Server 2014, SQL Server 2016 Preview
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
Performs a bitwise logical AND operation between two integer values.
Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.
The & bitwise operator performs a bitwise logical AND between the two expressions, taking each corresponding bit for both expressions. The bits in the result are set to 1 if and only if both bits (for the current bit being resolved) in the input expressions have a value of 1; otherwise, the bit in the result is set to 0.
If the left and right expressions have different integer data types (for example, the left expression is smallint and the right expression is int), the argument of the smaller data type is converted to the larger data type. In this case, the smallint expression is converted to an int.
The following example creates a table using the int data type to store the values and inserts two values into one row.
USE tempdb; GO IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'bitwise') DROP TABLE bitwise; GO CREATE TABLE bitwise ( a_int_value int NOT NULL, b_int_value int NOT NULL ); GO INSERT bitwise VALUES (170, 75); GO
This query performs the bitwise AND between the a_int_value and b_int_value columns.
USE tempdb; GO SELECT a_int_value & b_int_value FROM bitwise; GO
Here is the result set:
----------- 10 (1 row(s) affected)
The binary representation of 170 (a_int_value or A) is 0000 0000 1010 1010. The binary representation of 75 (b_int_value or B) is 0000 0000 0100 1011. Performing the bitwise AND operation on these two values produces the binary result 0000 0000 0000 1010, which is decimal 10.
(A & B) 0000 0000 1010 1010 0000 0000 0100 1011 ------------------- 0000 0000 0000 1010