Edit

Bit manipulation functions

  • Article

Applies to: SQL Server 2022 (16.x) Azure SQL Database Azure SQL Managed Instance SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric

Bit manipulation functions such as moving, retrieving (getting), setting, or counting single bits within an integer or binary value, allow you to process and store data more efficiently than with individual bits.

A bit has two values (1 or 0, which represent on or off, or true or false). A byte is made up of a sequence of 8 bits. Bit manipulation functions in SQL Server treat the "leftmost" bit in a byte as the biggest (the most significant). To the bit manipulation functions, bits are numbered from right to left, with bit 0 being the rightmost and the smallest and bit 7 being the leftmost and largest.

For example, a binary sequence of 00000111 is the decimal equivalent of the number 7. You can calculate this out using powers of 2 as follows:

00000111 = (2^2 + 2^1 + 2^0 = 4 + 2 + 1 = 7)

What this means in practice is that while SQL Server stores this value as 11100000 (byte-reversed), the bit manipulation functions will treat it as though it's 00000111.

When looking at multiple bytes, the first byte (reading left to right) is the biggest.

You can use the following images to visualize how SQL Server's bit manipulation functions interpret bit and byte expression values and bit offsets.

int

Diagram showing an int value where 4 bytes represent the reversed binary of each byte from left to right.

smallint

Diagram showing a small int value where the first byte represents bits 15 to 8, and the second byte represents bits 7 to 0.

Functions

There are five functions available for manipulating bits in SQL Server:

All five functions are intended to operate on tinyint, smallint, int, bigint, binary(n), and varbinary(n) data types.

The following types aren't supported: varchar, nvarchar, image, ntext, text, xml, and table.

Remarks

In the initial implementation, Distributed Query functionality for the bit manipulation functions within linked server or ad hoc queries (OPENQUERY) won't be supported.