Storage Engine

I was curious how SQL Server saves bit columns in a row. According to Microsoft Docs, it’s fairly straight-forward:

The SQL Server Database Engine optimizes storage of bit columns. If there are 8 or less bit columns in a table, the columns are stored as 1 byte. If there are from 9 up to 16 bit columns, the columns are stored as 2 bytes, and so on.

But how does the data actually look on the page? Let’s find out.

Here’s our test table:

USE [tempdb]
GO

CREATE TABLE [dbo].[TestBit] (
[TestID] [int] IDENTITY(1, 1) NOT NULL,
[BitField] [bit] NOT NULL DEFAULT(0),
CONSTRAINT [PK_TestBit] PRIMARY KEY CLUSTERED ([TestID] ASC)
)
GO

-- Insert four rows
INSERT INTO [dbo].[TestBit] DEFAULT VALUES
INSERT INTO [dbo].[TestBit] DEFAULT VALUES
INSERT INTO [dbo].[TestBit] DEFAULT VALUES
INSERT INTO [dbo].[TestBit] 1
GO

Now let’s have a look inside the row.

We can either use the classic DBCC IND method:

DBCC IND('tempdb', '[dbo].[TestBit]', 1);

Or we can use sys.dm_db_database_page_allocations on SQL Server 2012 (courtesy of Jason Strate):

SELECT allocated_page_file_id,
allocated_page_page_id
FROM sys.dm_db_database_page_allocations(DB_ID('tempdb'),
OBJECT_ID('[dbo].[TestBit]'), 1, NULL, 'DETAILED')
WHERE is_allocated = 1
AND page_type = 1;
GO

Notice how I asked for Page Type 1, which according to Paul Randal, is a data page.

My output to the modified Strate Script looks like this:

allocated_page_file_id	allocated_page_page_id
3	30

Now we output to the console:
DBCC TRACEON (3604)

DBCC PAGE(tempdb, 3, 30, 3)

Here’s the first row, with the bit column set to 0 (I have edited the output to just display the row data, highlighting the column data):

00000000: 10000900 01000000 00020000

Here’s the last row, with the bit column set to 1:

00000000: 10000900 04000000 01020000

(Back to Paul Randal’s series on the Storage Engine, in Anatomy of a Record, we know that the 02 in the above row, after the byte that stores our bit column, is part of the NULL bitmap. Two bytes count the number of columns in the record, which in this case is 2.)

Now let’s mix things up and see how the row looks with eight columns that are bits.

DROP TABLE [dbo].[TestBit]
GO

CREATE TABLE [dbo].[TestBit] (
[TestID] [int] IDENTITY(1, 1) NOT NULL,
[BitField1] [bit] NOT NULL DEFAULT(1),
[BitField2] [bit] NOT NULL DEFAULT(0),
[BitField3] [bit] NOT NULL DEFAULT(1),
[BitField4] [bit] NOT NULL DEFAULT(0),
[BitField5] [bit] NOT NULL DEFAULT(1),
[BitField6] [bit] NOT NULL DEFAULT(0),
[BitField7] [bit] NOT NULL DEFAULT(1),
[BitField8] [bit] NOT NULL DEFAULT(0),
CONSTRAINT [PK_TestBit] PRIMARY KEY CLUSTERED ([TestID] ASC)
)
GO

In our first example with the larger table, I’ll set the bits alternating on and off to see how it looks.

INSERT INTO [dbo].[TestBit] DEFAULT VALUES
GO

The DBCC PAGE output (edited again) shows us the following:

00000000: 10000900 01000000 55090000 00

(Notice the NULL bitmap now reflects that we have 9 columns.)

How about all of the bit columns set to 0, except the first one?

00000000: 10000900 01000000 01090000 00

All of them set to 1 except the first one?

00000000: 10000900 01000000 fe090000 00

All of them set to 0 except the last one?

00000000: 10000900 01000000 80090000 00

And finally, all of them set to 1 except the last one:

00000000: 10000900 01000000 7f090000 00

Books Online also says that if you have more than 8 bits in a row, additional bytes are assigned to accommodate each set of eight. Here’s what we get when we add just one more column to our table:

00000000: 10000a00 01000000 7f210a00 0000

(Our NULL bitmap shows 0a for 10 columns.)

I’ve now completely satisfied my curiosity about how bit columns are stored in SQL Server.

And don’t forget:

DBCC TRACEOFF (3604)
GO
DROP TABLE [dbo].[TestBit]
GO

Thank you for reading.

Photo by Vlad Tchompalov on Unsplash.

Read More »How SQL Server stores data types: bit columns