Skip to content

set-bit-transact-sql.md

Latest commit

 

History

History
96 lines (65 loc) · 4.12 KB

set-bit-transact-sql.md

File metadata and controls

96 lines (65 loc) · 4.12 KB
title SET_BIT (Transact-SQL)
description Transact-SQL reference for the SET_BIT function.
author thesqlsith
ms.author derekw
ms.reviewer randolphwest
ms.date 02/03/2025
ms.service sql
ms.subservice t-sql
ms.topic reference
ms.custom
ignite-2025
f1_keywords
SET_BIT
SET_BIT_TSQL
helpviewer_keywords
bit manipulation [SQL Server], set bit
SET_BIT function
bit shifting [SQL Server], set bit
dev_langs
TSQL
monikerRange >=sql-server-ver16 || >=sql-server-linux-ver16 || =azuresqldb-mi-current || =azuresqldb-current || =fabric || =fabric-sqldb

SET_BIT (Transact SQL)

[!INCLUDE SQL Server 2022, SQL Database, SQL Managed Instance FabricSE FabricDW FabricSQLDB]

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.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: 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

Distributed Query functionality for the bit manipulation functions within linked server or ad hoc queries (OPENQUERY) aren't supported.

Large object (LOB) data types in the Database Engine can store data that exceeds 8,000 bytes. These data types store data on a row-overflow data page. A LOB also encompasses data types that store data on dedicated LOB page structures, which use a text or an image pointer of in-row references to LOB data pages. For more information about data storage, see the Pages and extents architecture guide.

The bit manipulation functions operate on the tinyint, smallint, int, bigint, binary(n), and varbinary(n) data types. Large object (LOB) data types, such as varchar(max), nvarchar(max), varbinary(max), image, ntext, text, xml, and common language runtime (CLR) BLOB types, aren't 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.

Related Content