Minimum space occupied by data rows in SQL Server

What is the minimum space occupied by data rows? Let's take a look at the following classic data row structure (derived from InsideSqlServer). variable-length columns have additional space overhead, so variable-length fields are not considered. Calculate the space occupied by each data row after actual data is removed: Status A occupies one byte and status B occupies

What is the minimum space occupied by data rows? Let's take a look at the following classic data row structure (derived from Inside SQL Server). variable-length columns have additional space overhead, so variable-length fields are not considered. Calculate the space occupied by each data row after actual data is removed: Status A occupies one byte and status B occupies

What is the minimum space occupied by data rows?

Let's take a look at the following classic data row structure (derived from Inside SQL Server)



Columns of the variable length type have extra space overhead, So fields of the variable length are not considered. Calculate the space occupied by each data row except the actual data:

Status A occupies one byte, and status B occupies one byte. It is used to identify 2 bytes of fixed field length and 2 bytes of fixed columns, the NULL bitmap occupies at least 1 byte.
In this case, 1 + 1 + 2 + 2 + 1 = 7. That is to say, without considering the actual data, a data row occupies at least seven bytes. If the actual data length is 1, the space occupied by each row is 8 bytes. Is that true?

Now let's make an experiment to verify that if there are no special instructions below, all the descriptions are based on the premise that the data table does not have a clustered index.

Create a table:
Create Table table1
(
Col1 char (1)
)

Insert two rows of data:
Insertinto table11 values ('A ')
Insertinto table11 values ('B ')

Then run the dbcc page command to view the Table structure. (For convenience, I only keep the Data and Offset Table sections)

DATA:

Slot 0, Offset 0x60, Length 9, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP

Memory Dump @ 0x44EFC060

00000000 ....

Slot 1, Offset 0x69, Length 9, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP

Memory Dump @ 0x44EFC069

00000000 ....

Offset table:

Row-Offset

1 (0x1)-105 (0x69)

0 (0x0)-96 (0x60)

Length is followed by 9, that is to say, each data row occupies 9 bytes. By using the offset table part, we can also calculate that each data row occupies 9 bytes. Instead of the 8 bytes we calculated. Let's take slot0 as an example to check what the following data rows actually store:

10000500 610100fe 08 was found in our records when there were too many records ....

The first two digits 10 indicate that there is no variable length field, and the last two digits 00 are not used in sqlserver 2005; 0500 indicates that the length of the fixed length field is 5 (the actual length is increased by 4 ); 61 is converted to a decimal digit 97, indicating the lowercase letter. The following 0100 indicates that the number of fields with a fixed length is 1; the remaining fe is the NULL bitmap, which is translated into a binary number of 1111, which indicates that no field in this row is NULL, this is also consistent with the actual situation. So what does the last two 08 represent? Try again before making public answers


Create a table:
Create Table table1
(
Col1 char (2)
)

Insert two rows of data in the image table:
Insertinto table11 values ('A ')
Insertinto table11 values ('bb ')

Then run the dbcc page command to view the Table structure. (For convenience, I only keep the Data and Offset Table sections)

DATA:

Slot 0, Offset 0x60, Length 9, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP

Memory Dump @ 0x4500C060

00000000...

Slot 1, Offset 0x69, Length 9, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP

Memory Dump @ 0x4500C069

00000000...

Offset table:

Row-Offset

1 (0x1)-105 (0x69)

0 (0x0)-96 (0x60)

Now let's take a look at the storage of char (2). We can see through Length and offset table that the Length of the Data row is still 9. Analyze the specific data rows. The slot 0 is still used as an example:
10000600 61610100 fe was found to have been found in the upper limit of the upper limit...

The first two digits 10 indicate that there is no variable length field, and the last two digits 00 are not used in sqlserver 2005; 0600 indicates that the length of the fixed length field is 5 (the actual length is increased by 4 ); 61 is converted to a decimal digit 97, indicating the lowercase letter. The following 0100 indicates that the number of fields with a fixed length is 1; the remaining fe is the NULL bitmap, which is translated into a binary number of 1111, which indicates that no field in this row is NULL, this is also consistent with the actual situation.

The above two experiments show that there is only one field, and the data row of the table whose type and length are char (1) occupies space and only one field, in addition, the space occupied by data rows in tables of the type and length of char (2) is equal. Why is this happening?

In fact, if the length of a Data row does not reach the specified minimum length (9 bytes), SQL SERVER will automatically fill in a byte after the row and extend the length to 9. This is the reason why we saw the 08 problem. Therefore, the minimum length of SQL Server data rows is 9, not 8.

You may have to ask: Why does SQL SERVER have such a rule?

This rule is set up for the update operation. We know that the RID is used to identify the data page data row. When a row of data in the data page is updated so that the existing data page cannot accommodate this row, SQLSERVER moves the row to a new data page. The original data row location is not occupied by other data rows, but replaced with a forwarding pointer. This pointer Points to the new location of the updated data row. The size of this pointer is 9 bytes (the header occupies one byte, the RID occupies 8 bytes, and the RID consists of four bytes of pageID, two bytes of fileID, and two bytes of slotID ). Therefore, to ensure that data rows can be successfully replaced with forwarding pointer, SQL server requires at least nine bytes for each data row.

You may have to ask again why you need the bird forwarding pointer. It's okay to move it directly. There is no relationship between the data page and the data page, and the data lines on the data page are unordered. What is the use of the forwarding pointer ???

The reason is that when a row of data in the data page is updated so that the existing data page cannot accommodate the row, SQLSERVER will move the row to the new data page. Then there are two options:

1. Update the RID of all data rows on the original data page, which may be a very expensive operation (may have 249 indexes ).
2. If the RID in the data page is not updated, replace the vacant position with a forwarding pointer. Follow this pointer to find records for future queries.



You may have questions: If a clustered index is created for a table, will each data row occupy 9 bytes?
Yes. Although the update operations of clustered indexes are different, to facilitate the conversion of B-tree to heap, 9 bytes are required.

Http://www.cnblogs.com/stswordman/archive/2007/08/22/865425.html