SET_BIT (Transact SQL)
Applies to: 
SQL Server 2022 (16.x)
Azure SQL Database
Azure SQL Managed Instance
SQL analytics endpoint in Microsoft Fabric
Warehouse in Microsoft Fabric
SET_BIT returns expression_value offset by the bit defined by bit_offset. The bit value defaults to 1, or is set by bit_value.
Transact-SQL syntax conventions
Syntax
SET_BIT ( expression_value, bit_offset )
SET_BIT ( expression_value, bit_offset, bit_value )
Arguments
expression_value
Any integer or binary expression that isn't a large object (LOB).
bit_offset
Any integer.
Return types
The same type as expression_value.
The bit_offset parameter is used to identify the nth bit of the data to set. In integer types, the 0th bit is the least significant bit. In binary types, the 0th bit is the least significant bit in the rightmost byte.
bit_value can be an integer or a bit. However, the only valid values for bit_value are 1 and 0, regardless of the data type. SET_BIT will throw an error if bit_value isn't 1 or 0 or null.
SET_BIT will throw an error if bit_offset is negative or greater than the last bit in the data type.
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.
Examples
A. Use SET_BIT to modify a value
In this example, the third bit (at offset 2, zero-based index) is being set to 1.
SELECT SET_BIT ( 0x00, 2 ) as VARBIN1;
The result is 0x04. This is because the expression_value of 0x00 is converted to 0000. SET_BIT changes its third bit (offset 2) to 1, making it 0100. This binary value is then returned as 4 in hexadecimal representation.
B. Use SET_BIT to modify a value with a custom bit_value
In this example, the bit_value is being set to 0 instead of the default of 1.
SELECT SET_BIT ( 0xabcdef, 0, 0 ) as VARBIN2;
The result is 0xABCDEE. The expression_value is converted to binary, which is 1010 1011 1100 1101 1110 1111. SET_BIT changes the first bit to a 0, and the result is returned in hexadecimal format.