Shulou(Shulou.com)05/31 Report--

This article introduces the role of table and index storage structure in SQL Server. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

1. Heap

A heap is a table that does not contain a clustered index. There is a row in the sys.partitions of the heap, and for each partition used by the heap, there is index_id= 0. There is only one partition, and in the system table, each allocation unit under this partition has a connection to the Index Allocation Map page (IAM), which describes the zone information in the IAM page.

The column first_iam_page in the sys.system_internals_allocation_units system view points to the first page of a series of IAM pages that manage the allocated space for the heap in a particular partition. SQL Server uses IAM pages to move through the heap. The data pages and rows in the heap are not in any particular order and are not linked together. The only logical connection between data pages is the information recorded in the IAM page.

two。 Tables with nonclustered indexes

If there is a table that has only a nonclustered index but no clustered index, the corresponding index number is 2murme250. So for each nonclustered index, there is a corresponding partition, and in the system table, for each allocation unit under this partition, there is a connection to the root page. The data pages are connected with each other through the front and back pointers, which is a complete tree structure. At the bottom of the tree, there will be a connection to the real data in the form of file number + page number + line number, while the real data is stored as a heap. As shown in the following figure:

3. Table with clustered index

The clustered index in the table with the corresponding index number 1. It has a corresponding partition under which each allocation unit has a connection to the root page. For a clustered index, the leaf node holds real data, not a join like a nonclustered index. As shown in the following figure:

A nonclustered index and a clustered index have the same B-tree structure, and there are two significant differences between them:

The data rows of the underlying table are not sorted and stored in the order of non-clustered keys.

The leaf layer of a nonclustered index consists of index pages rather than data pages

Case study: let's look at the storage structure of a table. The table we use here is a production table with more than 100 million records. Check the object_ID of the table, as shown in the following figure:

I have partitioned this table to view its partition information. You can use the command shown in the following figure:

As you can see from the above figure, this table has 16 partitions, corresponding to different indexes, basically each partition has more than 10 million records. From this figure, you can also see that the ID of the heap or B-tree is the same as the partition ID. If you want to further view the specific information of an index, you can use the following command, such as viewing the information of 72057594067419136.

From this figure, we can see that there is only one allocation unit in this partition. IN_ROW_DATA indicates that this allocation unit is only used to store specific data, a total of 5353 pages, 5346 pages have been used, and data occupies 5320 pages.

If you want to see the location of the root page, you can use the following command:

However, it should be noted that the location of the root page shown here is 0xEC0100001100. Because of storage, it is parsed in reverse order, that is, 0x0011000001EC. The first two bytes indicate the filegroup number, and the last four bytes are the page number, that is, (1d0x01CE), which is replaced by decimal (1492). Then you can use the DBCC PAGE command we mentioned in the previous section to view the page information, as shown in the following figure:

You can see the specific data, and the return results of this interface will vary depending on the clustered and nonclustered indexes on the table. If you look at the total number of pages and extents used by a table, you can also use the command: DBCC SHOWCONFIG, as shown in the following figure:

In the case of the same table structure, the establishment of a clustered index will not increase the size of the table, but the establishment of a nonclustered index will increase a lot of space. In terms of performance, the SQL Server product group has been tested, and the performance of the clustered index is higher under select, update and delete operations. when inserting records, the performance of the clustered index is the same as that of the nonclustered index, and there is no phenomenon that the clustered index affects the insertion speed, but in the production environment. You still have to be careful.

On the role of table and index storage structure in SQL Server is shared here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.