Bit manipulation functions
SQL Server 2022 (16.x) Preview
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 (
0, which represent
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
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.
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.
In the initial implementation, Distributed Query functionality for the bit manipulation functions within linked server or ad hoc queries (OPENQUERY) won't be supported.
Submit and view feedback for