Storage size of bit type in SQL Server

For general int, Char, tinyint, and other data types, the storage space they occupy is measured in bytes, but the bit type is only 0 and 1 or
False and true. In this case, only one bit is required for representation.
How much space does the bit type occupy in the server? Is it stored by a bit? Or is it possible to use one byte for storage?

These two answers are incorrect !!! In fact, the space occupied by the bit type is related to the location of the table where the bit type column is located. In some cases, the bit occupies one byte, in some cases, bit actually occupies several BITs (several bit columns share one byte ). The following is a detailed analysis:

1. Individual bit columns occupy one byte. It means that neither the left-side fixed-length column nor the right-side fixed-length column of a bit-type column is a bit-type column. For example, a table:

Create Table TT
(
C1 int primary key,
C2 bit not null,
C3 char (2) not null
)

 

SQL
When the server stores data in a table, it first divides the columns in the table into a fixed length and a variable length (the variable length is an unfixed data type, for example
Varchar, nvarchar, varbinary, etc. When storing data on the data page, you must first store all the fixed-length data before storing the variable-length data. Here, the left of column C2
The edge is of the int type, and the right side is of the char type. They are of the Fixed Length and are not of the bit type. Therefore, a byte must be set between C1 and C3 to store C2, although C2 only uses one of them.

Let's verify whether it is as I said:

(1) Insert a row of data: insert into TT values (, 'A ')

(2) Find the file number and page number of the first page of TT table data (that is, the page where the inserted row of data is located:

Select first_page
From SYS. partitions P
Inner join SYS. system_internals_allocation_units
On P. partition_id = A. container_id
Where object_id = object_id ('dbo. tt ')

 

Here I return 0x76 00 00 00 01 00. here we need to reverse it to see 0x00 01 00 00 00 76. The first two bytes are the file number, followed by the page number, so the file number is 1 and the page number is 118 (convert 0x76 to decimal to 118)

(3) run the DBCC page command to view the internal structure of the page:

DBCC traceon (3604)
DBCC page (testdb, 1,118, 3)

 

Here DBCC
Traceon (3604) indicates that the page content is directly output. testdb is the database where the TT table I created is located, as mentioned earlier than 1 and 118. The last one is the print option. 0 indicates only
The output page header; 1 does not output all the content, but outputs the content with data; 2 indicates the complete output of the page content, and 3 is similar to 1, however, each record must list the column values separately. The following are output requirements:
Important Content:

00000000: 0000b00 01000000 01616103 0000 million rows were too large...

I will not talk about the specific format of the data row here. in SQL Server 2005
The storage engine is described in detail. The data we inserted starts from 5th bytes and is 01000000 bytes.
016161. Here, 01000000 is C1, which occupies 4 bytes because it is of the int type. The following 01 is C2, which occupies 1 byte. Next, 6161 is C3.
.

2. if multiple bit columns are separated by variable-length data columns, remove the variable-length columns and check the number of continuous bit columns. Each column occupies one position, if there are eight extra columns, more bytes will be occupied. For example, to create a table like this:

Create Table VTT
(
C1 bit not null,
C2 varchar (5) not null,
C3 bit not null,
C4 nvarchar (5) null,
C5 bit null,
C6 int not null
)

 

After the variable-length column is filtered out, it becomes C1, C3, C5, and C6. Three bit columns are continuous, so C1, C3, and C5 share one byte. Next, let's verify:

(1) Insert an example data: insert into VTT values (1, 'abc', 1, n 'xyz)

(2) Use the preceding SQL statement. In the same way, find the first page of the VTT table: 0xc00000000100, corresponding to file number 1 and page number 192.

(3) view internal data on this page:

00000000: 30000900 03ff0300 00060000 02001500 running 0 ..............
00000010: 1b006162 63780079 007a00 00000000000000000000000000000000000000000000000000000000capacity... abcx. Y. Z.

The inserted data starts from 5th bytes ......
Here 03 is the data of C1, C3, and C5, and 03 is converted to binary 00000011. The C1 column corresponds to the second digit 1, C3 corresponds to the second to the last digit 1, and C5 corresponds to the third to the last digit 0. Next
The ff of is 1023 of the C6 value. The following are columns, null bitmaps, and variable-length columns. Here we will discuss bit space usage, so we will not explain the following.

3. A table has multiple bit columns. whether the order of these columns is continuous determines whether the bit can share one byte. In SQL Server, data is stored in the column order. The first and last columns are BIT data type columns and cannot share one byte.

That is to say, the following table T1 and table T2 occupy different spaces. T1 data occupies 7 bytes and T2 data occupies 8 bytes.

Create Table T1
(
C1 int primary key,
C2 bit not null,
C3 bit not null,
C4 char (2) not null
)

Create Table T2
(
C1 int primary key,
C2 bit not null,
C4 char (2) not null,
C3 bit not null
)

However, in the following Table T3 and T4, the data space occupied by their bit columns is the same because the data type is variable-length in the middle.

Create Table T3
(
C1 int not null,
C2 bit not null,
C3 varchar (2) not null,
C4 bit not null
)
Create Table T4
(
C1 int not null,
C2 bit not null,
C4 bit not null,
C3 varchar (2) not null
)