SQL BIT

What is the SQL BIT data type?

BIT is a fixed-length binary data type that stores 1 or more individual bits, often used for Boolean flags or compact bit fields.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL BIT:

SQL BIT Full Explanation

BIT is a standard SQL data type representing fixed-length sequences of bits (0 or 1). Depending on the dialect, BIT can store a single Boolean value or a bit array of length n. When declared without a length, most engines default to 1 bit. Storage is highly compact: SQL Server stores up to 8 BIT columns in 1 byte, while MySQL and PostgreSQL pack bits into bytes as needed. Comparing BIT to BOOLEAN: ANSI SQL defines BOOLEAN separately, but many databases map BOOLEAN to BIT(1) behind the scenes (SQL Server) or support both (PostgreSQL). BIT values are manipulated with binary literals (B'1', 0b0101), numeric literals (0 or 1), or bitwise operators (&, |, ^) depending on the dialect. Caveats: BIT is not portable in DDL or literals across systems, some dialects silently pad or truncate bit strings, and NULL is distinct from 0. Indexing BIT(1) columns offers limited selectivity, so composite indexes or alternative types may be preferred.

SQL BIT Syntax

-- Column definition
column_name BIT;              -- default length 1
column_name BIT(n);           -- fixed length n bits (n > 0)

-- Cast or literal examples (dialect specific)
SELECT B'1010'::BIT(4);       -- PostgreSQL
SELECT 0b1;                   -- MySQL binary literal

SQL BIT Parameters

n (integer) - Optional. Number of bits to store. Must be a positive integer.

Example Queries Using SQL BIT

-- 1. Single Boolean flag
CREATE TABLE accounts (
    id INT PRIMARY KEY,
    is_active BIT DEFAULT 1
);

-- 2. Four-bit status code
CREATE TABLE sensors (
    sensor_id INT PRIMARY KEY,
    status BIT(4) NOT NULL             -- 0000 to 1111
);

-- 3. Update a BIT value (SQL Server style)
UPDATE accounts SET is_active = 0 WHERE id = 42;

-- 4. Insert bit string literal (PostgreSQL)
INSERT INTO sensors VALUES (10, B'1010');

Expected Output Using SQL BIT

Tables are created with compact BIT columns
Inserts succeed, storing 0/1 or bit strings as binary data
Queries return bit representations or automatically cast values to 0/1 depending on client settings

Use Cases with SQL BIT

Store Boolean flags in a space-efficient way.
Pack multiple on/off attributes into a single BIT(n) column.
Represent low-level device status registers or binary feature vectors.
Perform bitwise operations for permissions, fitness masks, or feature toggles.

Common Mistakes with SQL BIT

Assuming BIT equals BOOLEAN across all databases. Some engines reject TRUE/FALSE literals for BIT.
Forgetting to specify length n, leading to default 1-bit columns when a bit array was intended.
Treating NULL the same as 0. NULL means unknown, not false.
Using BIT(1) as a primary filter where selectivity is low, resulting in poor index performance.
Mixing binary literal syntaxes between dialects (B'1010' vs 0b1010).

First Introduced In

SQL-92 standard; early vendor support in SQL Server 6.0

Frequently Asked Questions

When should I use BIT instead of BOOLEAN?

BIT is ideal for compact storage or packing several flags into one column. BOOLEAN offers clearer semantics for a single true or false value but is internally mapped to BIT(1) in some engines.

What is the default length of BIT?

If you omit the length, most databases create a 1-bit column, effectively storing a Boolean value.

How do I insert a bit literal?

Use dialect-specific syntax: B'1010' in PostgreSQL, 0b1010 in MySQL, or simply 0 and 1 in SQL Server, which automatically casts integers to BIT.

Is BIT portable across databases?

The keyword exists across major systems, but literal formats, maximum lengths, and casting behaviors differ. Always validate when migrating schemas.