SQL Server storage mechanism

SQL Server storage mechanism

　　1. Section

A section (extent) is a basic storage unit used to allocate space for tables and indexes. It consists of 8 contiguous 64KB data pages.

The main points of the concept of allocating space based on segments rather than actual use of space:

1. Once the section is full, the space that the next record will occupy is not the size of the record, but the size of the whole new zone. Assign one segment at a time instead of assigning a record.
2. By pre-allocating space, SQL Server saves time to allocate new space for each record.

Adding too many rows to the currently allocated section is going to take up the entire segment, which seems like a waste, but this method wastes little space. However, these wasted spaces accumulate, especially in more fragmented environments.

The advantage of taking up all the space is that SQL Server eliminates the overhead of allocating time and does not need to consider allocation issues every time a row is written, and SQL Server processes additional space allocations only when a new section is needed.

Do not confuse the space occupied by the section with the space occupied by the database, and the space allocated to the database is the amount of free space that the disk drive will have to reduce. A section is simply a way to allocate again within the entire space that the database retains.

　　2. Page

A page is an allocation unit in a specific section. Each section contains 8 pages.

It is also the last storage level that can be reached before the actual data row is reached. Although the number of pages in each section is fixed, the numbers of rows in each page are not fixed. This depends on the size of the row, and the size of the row can vary. You can think of a page as a container for table rows and index row data. Cross-line is not normally allowed.

Common page types:

1. Data pages: Data is also not to be interpreted-they are the actual data in the table, except for any BLOB data that is not defined with the text in row option, varchar (max), or varbinary (max).

2. Index pages: Index pages are also fairly intuitive-they include both non-page and page-level pages for non-clustered indexes, and non-page-level pages for clustered indexes.

　　In SQL Server, the smallest unit of storage is the page. SQL Server reads the page, either the entire read, or completely without reading, without compromise.

The size of the database page is 8K, and the actual amount of data that can be stored is 1024*8=8192-header (96)-line offset (36) = 8060 bytes.

　　Page splits

When the page is full, it is split. This means that multiple new pages are assigned-and also means that nearly half of the data on the existing page is moved to the new page.

There are exceptions when using clustered indexes. If you have a clustered index and the next inserted record is physically the last record in the table, create a new page and add the new row to the new page without relocating any existing data.

　　3, line

A row is a row of records in a table. The maximum line can be up to 8KB. In addition to the 8060-character limit, there is a maximum of 1024 standard columns (non-sparse columns), and the column is a column of data for the table, which is the column you understand. In fact, it is not uncommon to run out of columns before breaking the 8060 character limit. 1024 provides an average column width of less than 8 bytes. 1024 columns, a table is usually only more than 10 columns ah. When you break the 1024 columns, you apply the sparse columns.

　　4. Sparse columns

Sparse columns are a special kind of data structure introduced by SQL Server2008. They are primarily used to handle repetitive scenarios where the columns are only occasionally used. Most of the time it's empty. Many times, once a small number of such columns are encountered, it means that a large number of such columns may be encountered in the future. With sparse columns, you can increase the total number of columns allowed in a single table to 30000. What's the concept of 30,000 columns? What system is used to get so many columns.

The Image, text, ntext, geography, geometry, timestamp, and all user custom data types cannot be marked as sparse columns.

SQL Server storage mechanism