SQL storage principle and relationship of clustered index, nonclustered index, unique index, PRIMARY KEY constraint (new)

Heaps (heap)

This structure is called a heap because it is not arranged in a logical order that is specified by anyone. Instead, they are organized according to partitioned data. In other words, the physical order of the disks is followed. As long as the data file that needs to be read does not have file system fragmentation (note the fragmentation of the index mentioned below), the read process can be carried out continuously on disk without the extra disk arm movement . The disk arm movement is the most expensive operation in the I/O operation.

The heap uses a bitmap structure to manage the allocation of data. That is, it will tell you two results, whether the area is assigned or not assigned. The physical order in each of these zones is as follows.

(The number in the second line of the graph is wrong and should be added 1)

Ps:iam Chain Knowledge

The Sytem_internals_allocation_units table holds a pointer to the First data page and the first IAM page . IAM holds pointers to data pages in the order in which they are data pages. There is no direct link between the data pages.

Next, look at the IAM information, as follows:

Slot 0 = (1:79) Slot 1 = (1:89) Slot 2 = (1:90)

Slot 3 = (1:93) Slot 4 = (1:94) Slot 5 = (1:109)

Slot 6 = (1:110) Slot 7 = (1:114)

Iam:extent Alloc Status Slot 1 @0x592ec0c2

(1:0)-(1:176) = Not allocated

(1:184)-(1:192) = Allocated

(1:200)-(1:376) = Not allocated

The Highlight section indicates the partition information for IAM, and the first data page pointer points to the page. This is consistent with the first_page values we have queried . An IAM page corresponds to 8 data pages, and when there are more than 8 data pages, the system assigns a uniform area page from its corresponding 4GB space (about 512000 pages). Creates a new IAM page when the data page exceeds the number of pages that can be allocated .

There are only 8 slot,8 temporary storage data pages, when the allocation data page more than 8 mixed partitions, the system will assign a unified partition for the data table. (Each allocation interval here is calculated from the first start of the zone, for example, 192 that refers to 192-199)

~191, 192~199 two unified partitions. Each partition contains 8 page, plus 8 pages. This 25 data pages, plus 1 Iam page altogether used 20 pages. Because the unified partition is assigned sequentially,  ~199< The page for span lang= "ZH-CN" xml:lang= "ZH-CN" is not used. Use Dbcc

Below we delete the inserted data, and then look at the page assignment of IAM, and find that the page assignment is not changed because of the data deletion, but the data page is simply emptying the data.

SQL storage principle and relationship of clustered index, nonclustered index, unique index, PRIMARY KEY constraint (new)