Storage size of BIT type in SQLServer

For general INT, CHAR, tinyint, and other data types, the storage space they occupy is measured in bytes, but BIT type is only 0, 1 or false or true, in this case, only one Bit is required to represent the BIT type. How much space does the Bit type occupy in SQLServer? Is it stored by a Bit?

For general INT, CHAR, tinyint, and other data types, the storage space they occupy is measured in bytes, but BIT type is only 0, 1 or false or true, in this case, only one Bit is required to represent the BIT type. How much space does the Bit type occupy in SQL Server? Is it stored by a Bit?

For general INT, CHAR, tinyint, and other dataType, What they occupyStorageThe space is measured in bytes, but BITTypeSince there are only 0, 1, false, and true, in this case, only one Bit is required to represent it. in SQL Server, BITTypeHow much space does it occupy? Is it a Bit?Storage? Or it may be a byte.Storage?

These two answers are incorrect! ActuallyBITTypeOccupied Space and BITTypeIn some cases, BIT occupies one byte, and in some cases BIT actually occupies several BITsType). The following is a detailed analysis:

　　1. Separate BITTypeThe column occupies one byte.

A BITTypeThe left-side and right-side fixed-length columns of are not BITType. For example, a table:

Create table tt ( C1 int primary key, C2 bit not null, C3 CHAR (2) NOT NULL )

SQL ServerInStorageTableFor data in a table, the columns in the table are divided into fixed and variable-length data according to the original sequence.Type, Such as varchar, nvarchar, and varbinary. On the data pageStorageData firstStorageAll the fixed-length data, and thenStorageVariable Length data. Here, the left side of column c2 is int.Type, Char on the rightType, Are fixed length, and not BITTypeTherefore, a byte must be set between c1 and c3.StorageC2, 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 first page of tt table data(That is, the page where the inserted row of data is located) file number and page number:

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 above 1 and 118. The last one is the print option. 0 indicates that only the page header is output; 1 indicates that no content is output, but the content with data is output; 2 indicates that the content of the page is completely output, and 3 indicates that it is similar to 1, however, each record must list the column values separately. The following are output content that requires attention:

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 2005StorageEngine is described in detail. The data we inserted starts from 5th bytes, Which is 01000000 016161. Here 01000000 is c1, because it is intTypeSo it occupies 4 bytes. The following 01 is c2, which occupies 1 byte. Next, 6161 is c3.

　　2. Multiple bitsTypeUse variable-length data between columnsTypeIf the columns are separated, remove the variable-length columns and check the continuous BIT values.TypeThe number of columns. Each column occupies a single bit. If there are eight extra columns, it occupies more bytes.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, with three bitsTypeColumns 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. The next ff is the c6 value 1023. 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 bits.TypeWhether the order of the columns determines whether BIT can share a byte. SQL Server in column orderStorageThe first and last columns are BIT data.TypeColumn, 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 variable length data is in the middle.TypeSo their BIT columns occupy the same data space.

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 )