Skip to content

Latest commit

 

History

History
86 lines (59 loc) · 3.68 KB

bit-count-transact-sql.md

File metadata and controls

86 lines (59 loc) · 3.68 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs monikerRange
BIT_COUNT (Transact-SQL)
Transact-SQL reference for the BIT_COUNT function.
thesqlsith
derekw
randolphwest
02/03/2025
sql
t-sql
reference
BIT_COUNT
BIT_COUNT_TSQL
bit manipulation [SQL Server], bit count
BIT_COUNT function
bit shifting [SQL Server], bit count
TSQL
>=sql-server-ver16 || >=sql-server-linux-ver16 || =azuresqldb-mi-current || =azuresqldb-current || =fabric

BIT_COUNT (Transact SQL)

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

BIT_COUNT takes one parameter and returns the number of bits set to 1 in that parameter as a bigint type.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

BIT_COUNT ( expression_value )

Arguments

expression_value

Any integer or binary expression that isn't a large object (LOB).

Return types

bigint

BIT_COUNT doesn't cast, before counting the number of bits. This is because the same number can have a different number of ones in its binary representation depending on the data type.

For example, SELECT BIT_COUNT (CAST (-1 AS SMALLINT)) and SELECT BIT_COUNT (CAST (-1 AS INT)) return 16 and 32 respectively. This is intended, as the binary representation of -1 can have a different number of bits set to 1 depending on 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. Calculate the BIT_COUNT in a binary value

In the following example, the number of bits set to 1 in a binary value are calculated.

SELECT BIT_COUNT(0xabcdef) AS Count;

The result is 17. This is because 0xabcdef in binary is 1010 1011 1100 1101 1110 1111, and there are 17 bits with a value set to 1.

B. Calculate the BIT_COUNT in an integer

In the following example, the number of bits set to 1 in an integer are calculated.

SELECT BIT_COUNT(17) AS Count;

The result is 2. This is because 17 in binary is 0001 0001, and there are only 2 bits with a value set to 1.

Related content