Skip to content
Home » How SQL Server stores data types: bit columns

How SQL Server stores data types: bit columns

LED light panel

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.

If you’re curious, here’s a reference table to explain the hex values you saw above:

8 7 6 5 4 3 2 1 DEC CHAR DEC2BIN DEC2HEX
0 0 0 0 0 0 0 0 0 00000000 0
0 0 0 0 0 0 0 1 1  00000001 1
0 0 0 0 0 0 1 0 2  00000010 2
0 0 0 0 0 0 1 1 3  00000011 3
0 0 0 0 0 1 0 0 4  00000100 4
0 0 0 0 0 1 0 1 5  00000101 5
0 0 0 0 0 1 1 0 6  00000110 6
0 0 0 0 0 1 1 1 7  00000111 7
0 0 0 0 1 0 0 0 8  00001000 8
0 0 0 0 1 0 0 1 9 00001001 9
0 0 0 0 1 0 1 0 10 00001010 A
0 0 0 0 1 0 1 1 11 00001011 B
0 0 0 0 1 1 0 0 12 00001100 C
0 0 0 0 1 1 0 1 13 00001101 D
0 0 0 0 1 1 1 0 14  00001110 E
0 0 0 0 1 1 1 1 15  00001111 F
0 0 0 1 0 0 0 0 16  00010000 10
0 0 0 1 0 0 0 1 17  00010001 11
0 0 0 1 0 0 1 0 18  00010010 12
0 0 0 1 0 0 1 1 19  00010011 13
0 0 0 1 0 1 0 0 20  00010100 14
0 0 0 1 0 1 0 1 21  00010101 15
0 0 0 1 0 1 1 0 22  00010110 16
0 0 0 1 0 1 1 1 23  00010111 17
0 0 0 1 1 0 0 0 24  00011000 18
0 0 0 1 1 0 0 1 25  00011001 19
0 0 0 1 1 0 1 0 26 00011010 1A
0 0 0 1 1 0 1 1 27  00011011 1B
0 0 0 1 1 1 0 0 28  00011100 1C
0 0 0 1 1 1 0 1 29  00011101 1D
0 0 0 1 1 1 1 0 30  00011110 1E
0 0 0 1 1 1 1 1 31  00011111 1F
0 0 1 0 0 0 0 0 32   00100000 20
0 0 1 0 0 0 0 1 33 ! 00100001 21
0 0 1 0 0 0 1 0 34 “ 00100010 22
0 0 1 0 0 0 1 1 35 # 00100011 23
0 0 1 0 0 1 0 0 36 $ 00100100 24
0 0 1 0 0 1 0 1 37 % 00100101 25
0 0 1 0 0 1 1 0 38 & 00100110 26
0 0 1 0 0 1 1 1 39 ‘ 00100111 27
0 0 1 0 1 0 0 0 40 ( 00101000 28
0 0 1 0 1 0 0 1 41 ) 00101001 29
0 0 1 0 1 0 1 0 42 * 00101010 2A
0 0 1 0 1 0 1 1 43 + 00101011 2B
0 0 1 0 1 1 0 0 44 , 00101100 2C
0 0 1 0 1 1 0 1 45 – 00101101 2D
0 0 1 0 1 1 1 0 46 . 00101110 2E
0 0 1 0 1 1 1 1 47 / 00101111 2F
0 0 1 1 0 0 0 0 48 0 00110000 30
0 0 1 1 0 0 0 1 49 1 00110001 31
0 0 1 1 0 0 1 0 50 2 00110010 32
0 0 1 1 0 0 1 1 51 3 00110011 33
0 0 1 1 0 1 0 0 52 4 00110100 34
0 0 1 1 0 1 0 1 53 5 00110101 35
0 0 1 1 0 1 1 0 54 6 00110110 36
0 0 1 1 0 1 1 1 55 7 00110111 37
0 0 1 1 1 0 0 0 56 8 00111000 38
0 0 1 1 1 0 0 1 57 9 00111001 39
0 0 1 1 1 0 1 0 58 : 00111010 3A
0 0 1 1 1 0 1 1 59 ; 00111011 3B
0 0 1 1 1 1 0 0 60 < 00111100 3C
0 0 1 1 1 1 0 1 61 = 00111101 3D
0 0 1 1 1 1 1 0 62 > 00111110 3E
0 0 1 1 1 1 1 1 63 ? 00111111 3F
0 1 0 0 0 0 0 0 64 @ 01000000 40
0 1 0 0 0 0 0 1 65 A 01000001 41
0 1 0 0 0 0 1 0 66 B 01000010 42
0 1 0 0 0 0 1 1 67 C 01000011 43
0 1 0 0 0 1 0 0 68 D 01000100 44
0 1 0 0 0 1 0 1 69 E 01000101 45
0 1 0 0 0 1 1 0 70 F 01000110 46
0 1 0 0 0 1 1 1 71 G 01000111 47
0 1 0 0 1 0 0 0 72 H 01001000 48
0 1 0 0 1 0 0 1 73 I 01001001 49
0 1 0 0 1 0 1 0 74 J 01001010 4A
0 1 0 0 1 0 1 1 75 K 01001011 4B
0 1 0 0 1 1 0 0 76 L 01001100 4C
0 1 0 0 1 1 0 1 77 M 01001101 4D
0 1 0 0 1 1 1 0 78 N 01001110 4E
0 1 0 0 1 1 1 1 79 O 01001111 4F
0 1 0 1 0 0 0 0 80 P 01010000 50
0 1 0 1 0 0 0 1 81 Q 01010001 51
0 1 0 1 0 0 1 0 82 R 01010010 52
0 1 0 1 0 0 1 1 83 S 01010011 53
0 1 0 1 0 1 0 0 84 T 01010100 54
0 1 0 1 0 1 0 1 85 U 01010101 55
0 1 0 1 0 1 1 0 86 V 01010110 56
0 1 0 1 0 1 1 1 87 W 01010111 57
0 1 0 1 1 0 0 0 88 X 01011000 58
0 1 0 1 1 0 0 1 89 Y 01011001 59
0 1 0 1 1 0 1 0 90 Z 01011010 5A
0 1 0 1 1 0 1 1 91 [ 01011011 5B
0 1 0 1 1 1 0 0 92 \ 01011100 5C
0 1 0 1 1 1 0 1 93 ] 01011101 5D
0 1 0 1 1 1 1 0 94 ^ 01011110 5E
0 1 0 1 1 1 1 1 95 _ 01011111 5F
0 1 1 0 0 0 0 0 96 ` 01100000 60
0 1 1 0 0 0 0 1 97 a 01100001 61
0 1 1 0 0 0 1 0 98 b 01100010 62
0 1 1 0 0 0 1 1 99 c 01100011 63
0 1 1 0 0 1 0 0 100 d 01100100 64
0 1 1 0 0 1 0 1 101 e 01100101 65
0 1 1 0 0 1 1 0 102 f 01100110 66
0 1 1 0 0 1 1 1 103 g 01100111 67
0 1 1 0 1 0 0 0 104 h 01101000 68
0 1 1 0 1 0 0 1 105 i 01101001 69
0 1 1 0 1 0 1 0 106 j 01101010 6A
0 1 1 0 1 0 1 1 107 k 01101011 6B
0 1 1 0 1 1 0 0 108 l 01101100 6C
0 1 1 0 1 1 0 1 109 m 01101101 6D
0 1 1 0 1 1 1 0 110 n 01101110 6E
0 1 1 0 1 1 1 1 111 o 01101111 6F
0 1 1 1 0 0 0 0 112 p 01110000 70
0 1 1 1 0 0 0 1 113 q 01110001 71
0 1 1 1 0 0 1 0 114 r 01110010 72
0 1 1 1 0 0 1 1 115 s 01110011 73
0 1 1 1 0 1 0 0 116 t 01110100 74
0 1 1 1 0 1 0 1 117 u 01110101 75
0 1 1 1 0 1 1 0 118 v 01110110 76
0 1 1 1 0 1 1 1 119 w 01110111 77
0 1 1 1 1 0 0 0 120 x 01111000 78
0 1 1 1 1 0 0 1 121 y 01111001 79
0 1 1 1 1 0 1 0 122 z 01111010 7A
0 1 1 1 1 0 1 1 123 { 01111011 7B
0 1 1 1 1 1 0 0 124 | 01111100 7C
0 1 1 1 1 1 0 1 125 } 01111101 7D
0 1 1 1 1 1 1 0 126 ~ 01111110 7E
0 1 1 1 1 1 1 1 127  01111111 7F
1 0 0 0 0 0 0 0 128 € 10000000 80
1 0 0 0 0 0 0 1 129  10000001 81
1 0 0 0 0 0 1 0 130 ‚ 10000010 82
1 0 0 0 0 0 1 1 131 ƒ 10000011 83
1 0 0 0 0 1 0 0 132 „ 10000100 84
1 0 0 0 0 1 0 1 133 … 10000101 85
1 0 0 0 0 1 1 0 134 † 10000110 86
1 0 0 0 0 1 1 1 135 ‡ 10000111 87
1 0 0 0 1 0 0 0 136 ˆ 10001000 88
1 0 0 0 1 0 0 1 137 ‰ 10001001 89
1 0 0 0 1 0 1 0 138 Š 10001010 8A
1 0 0 0 1 0 1 1 139 ‹ 10001011 8B
1 0 0 0 1 1 0 0 140 Π10001100 8C
1 0 0 0 1 1 0 1 141  10001101 8D
1 0 0 0 1 1 1 0 142 Ž 10001110 8E
1 0 0 0 1 1 1 1 143  10001111 8F
1 0 0 1 0 0 0 0 144  10010000 90
1 0 0 1 0 0 0 1 145 ‘ 10010001 91
1 0 0 1 0 0 1 0 146 ’ 10010010 92
1 0 0 1 0 0 1 1 147 “ 10010011 93
1 0 0 1 0 1 0 0 148 ” 10010100 94
1 0 0 1 0 1 0 1 149 • 10010101 95
1 0 0 1 0 1 1 0 150 – 10010110 96
1 0 0 1 0 1 1 1 151 — 10010111 97
1 0 0 1 1 0 0 0 152 ˜ 10011000 98
1 0 0 1 1 0 0 1 153 ™ 10011001 99
1 0 0 1 1 0 1 0 154 š 10011010 9A
1 0 0 1 1 0 1 1 155 › 10011011 9B
1 0 0 1 1 1 0 0 156 œ 10011100 9C
1 0 0 1 1 1 0 1 157  10011101 9D
1 0 0 1 1 1 1 0 158 ž 10011110 9E
1 0 0 1 1 1 1 1 159 Ÿ 10011111 9F
1 0 1 0 0 0 0 0 160 10100000 A0
1 0 1 0 0 0 0 1 161 ¡ 10100001 A1
1 0 1 0 0 0 1 0 162 ¢ 10100010 A2
1 0 1 0 0 0 1 1 163 £ 10100011 A3
1 0 1 0 0 1 0 0 164 ¤ 10100100 A4
1 0 1 0 0 1 0 1 165 ¥ 10100101 A5
1 0 1 0 0 1 1 0 166 ¦ 10100110 A6
1 0 1 0 0 1 1 1 167 § 10100111 A7
1 0 1 0 1 0 0 0 168 ¨ 10101000 A8
1 0 1 0 1 0 0 1 169 © 10101001 A9
1 0 1 0 1 0 1 0 170 ª 10101010 AA
1 0 1 0 1 0 1 1 171 « 10101011 AB
1 0 1 0 1 1 0 0 172 ¬ 10101100 AC
1 0 1 0 1 1 0 1 173 ­ 10101101 AD
1 0 1 0 1 1 1 0 174 ® 10101110 AE
1 0 1 0 1 1 1 1 175 ¯ 10101111 AF
1 0 1 1 0 0 0 0 176 ° 10110000 B0
1 0 1 1 0 0 0 1 177 ± 10110001 B1
1 0 1 1 0 0 1 0 178 ² 10110010 B2
1 0 1 1 0 0 1 1 179 ³ 10110011 B3
1 0 1 1 0 1 0 0 180 ´ 10110100 B4
1 0 1 1 0 1 0 1 181 µ 10110101 B5
1 0 1 1 0 1 1 0 182 10110110 B6
1 0 1 1 0 1 1 1 183 · 10110111 B7
1 0 1 1 1 0 0 0 184 ¸ 10111000 B8
1 0 1 1 1 0 0 1 185 ¹ 10111001 B9
1 0 1 1 1 0 1 0 186 º 10111010 BA
1 0 1 1 1 0 1 1 187 » 10111011 BB
1 0 1 1 1 1 0 0 188 ¼ 10111100 BC
1 0 1 1 1 1 0 1 189 ½ 10111101 BD
1 0 1 1 1 1 1 0 190 ¾ 10111110 BE
1 0 1 1 1 1 1 1 191 ¿ 10111111 BF
1 1 0 0 0 0 0 0 192 À 11000000 C0
1 1 0 0 0 0 0 1 193 Á 11000001 C1
1 1 0 0 0 0 1 0 194 Â 11000010 C2
1 1 0 0 0 0 1 1 195 Ã 11000011 C3
1 1 0 0 0 1 0 0 196 Ä 11000100 C4
1 1 0 0 0 1 0 1 197 Å 11000101 C5
1 1 0 0 0 1 1 0 198 Æ 11000110 C6
1 1 0 0 0 1 1 1 199 Ç 11000111 C7
1 1 0 0 1 0 0 0 200 È 11001000 C8
1 1 0 0 1 0 0 1 201 É 11001001 C9
1 1 0 0 1 0 1 0 202 Ê 11001010 CA
1 1 0 0 1 0 1 1 203 Ë 11001011 CB
1 1 0 0 1 1 0 0 204 Ì 11001100 CC
1 1 0 0 1 1 0 1 205 Í 11001101 CD
1 1 0 0 1 1 1 0 206 Î 11001110 CE
1 1 0 0 1 1 1 1 207 Ï 11001111 CF
1 1 0 1 0 0 0 0 208 Ð 11010000 D0
1 1 0 1 0 0 0 1 209 Ñ 11010001 D1
1 1 0 1 0 0 1 0 210 Ò 11010010 D2
1 1 0 1 0 0 1 1 211 Ó 11010011 D3
1 1 0 1 0 1 0 0 212 Ô 11010100 D4
1 1 0 1 0 1 0 1 213 Õ 11010101 D5
1 1 0 1 0 1 1 0 214 Ö 11010110 D6
1 1 0 1 0 1 1 1 215 × 11010111 D7
1 1 0 1 1 0 0 0 216 Ø 11011000 D8
1 1 0 1 1 0 0 1 217 Ù 11011001 D9
1 1 0 1 1 0 1 0 218 Ú 11011010 DA
1 1 0 1 1 0 1 1 219 Û 11011011 DB
1 1 0 1 1 1 0 0 220 Ü 11011100 DC
1 1 0 1 1 1 0 1 221 Ý 11011101 DD
1 1 0 1 1 1 1 0 222 Þ 11011110 DE
1 1 0 1 1 1 1 1 223 ß 11011111 DF
1 1 1 0 0 0 0 0 224 à 11100000 E0
1 1 1 0 0 0 0 1 225 á 11100001 E1
1 1 1 0 0 0 1 0 226 â 11100010 E2
1 1 1 0 0 0 1 1 227 ã 11100011 E3
1 1 1 0 0 1 0 0 228 ä 11100100 E4
1 1 1 0 0 1 0 1 229 å 11100101 E5
1 1 1 0 0 1 1 0 230 æ 11100110 E6
1 1 1 0 0 1 1 1 231 ç 11100111 E7
1 1 1 0 1 0 0 0 232 è 11101000 E8
1 1 1 0 1 0 0 1 233 é 11101001 E9
1 1 1 0 1 0 1 0 234 ê 11101010 EA
1 1 1 0 1 0 1 1 235 ë 11101011 EB
1 1 1 0 1 1 0 0 236 ì 11101100 EC
1 1 1 0 1 1 0 1 237 í 11101101 ED
1 1 1 0 1 1 1 0 238 î 11101110 EE
1 1 1 0 1 1 1 1 239 ï 11101111 EF
1 1 1 1 0 0 0 0 240 ð 11110000 F0
1 1 1 1 0 0 0 1 241 ñ 11110001 F1
1 1 1 1 0 0 1 0 242 ò 11110010 F2
1 1 1 1 0 0 1 1 243 ó 11110011 F3
1 1 1 1 0 1 0 0 244 ô 11110100 F4
1 1 1 1 0 1 0 1 245 õ 11110101 F5
1 1 1 1 0 1 1 0 246 ö 11110110 F6
1 1 1 1 0 1 1 1 247 ÷ 11110111 F7
1 1 1 1 1 0 0 0 248 ø 11111000 F8
1 1 1 1 1 0 0 1 249 ù 11111001 F9
1 1 1 1 1 0 1 0 250 ú 11111010 FA
1 1 1 1 1 0 1 1 251 û 11111011 FB
1 1 1 1 1 1 0 0 252 ü 11111100 FC
1 1 1 1 1 1 0 1 253 ý 11111101 FD
1 1 1 1 1 1 1 0 254 þ 11111110 FE
1 1 1 1 1 1 1 1 255 ÿ 11111111 FF
Tags: