Clustered index, non-clustered Index

Summary:

The clustered index is a clustered index.

A clustered index is an index created on a clustered index. When creating a clustered index, you need to re-Sort existing table data (if the table already has data ), that is, after the original table data is deleted, the sorting results are inserted in the physical order. Therefore, after the clustering index is created, all the data in the columns that create the clustering index is sorted in order.
A table can contain only one clustered index, but the index can contain multiple columns.
In B-tree indexes, the leaf layer of the clustered index is the data page.
Non-clustered indexes are similar to book indexes. Indexes and data are stored in different physical regions. Data is not sorted when a non-clustered index is created. A table contains multiple non-clustered indexes.
In B-tree indexes, the leaf layer of a non-clustered index is still an index page, which points to the actual storage location of the data page as a pointer.

Detailed explanation:

 

Non-clustered indexes and clustered indexes have the same B-tree structure, and the significant difference between them lies in the following two points:

Data rows in the base table are not sorted and stored in the order of non-clustered keys.

The leaf layer of a non-clustered index is composed of index pages rather than data pages.

Non-clustered indexes can be created on both the heap table structure and the clustered index table. Each index row in a non-clustered index contains non-clustered key values and row delimiters. This locator points to the data row containing the key value in the clustered index or heap.

If the table is a heap, the row locator is a pointer to the row. This pointer is generated by the File Identifier (ID), page number, and number of lines on the page. The entire pointer is called the row ID (RID ).

If the table package contains a clustered index, the row locator is the clustered index key of the row. If the clustered index is not a unique index, SQL Server adds the internally generated value (called a unique value) to make all duplicate keys unique. The values of these four bytes are invisible to users. This value is added only when the clustered key must be unique for non-clustered indexes. SQL Server uses the clustered index key stored in the leaf row of a non-clustered index to search for clustered indexes to retrieve data rows.

The page set of Tree B is located by the root_page pointer in the SYS. system_internals_allocation_units System View.

 

Heap table

-- Create a heap table

Create Table testheapindex

(

Name char (200 ),

Type1 char (900 ),

Type2 char (900)

)

-- Create a unique index and a non-unique index respectively

Create unique index idx_testheapindex1 on testheapindex (type1)

Create index idx_testheapindex2 on testheapindex (type2)

-- Insert Test Data

Insert into testheapindex values (A, a1, a2)

Insert into testheapindex values (B, b1, b2)

Insert into testheapindex values (C, C1, B2)

Insert into testheapindex values (D, D1, B2)

Insert into testheapindex values (E, E1, C2)

Insert into testheapindex values (F, F1, F1)

Insert into testheapindex values (G, G1, G1)

Insert into testheapindex values (H, H1, G1)

Insert into testheapindex values (I, i1, G1)

Insert into testheapindex values (J, J1, J1)

-- Obtain the corresponding page information of the table

Select a. Name table_name, B. Name index_name, B. index_id

From SYS. Objects A, SYS. Indexes B

Where a. object_id = B. object_id and A. Name = testheapindex

Truncate table tablepage;

Insert into tablepage exec (DBCC Ind (testdb, testheapindex, 0 ));

Insert into tablepage exec (DBCC Ind (testdb, testheapindex, 2 ));

Insert into tablepage exec (DBCC Ind (testdb, testheapindex, 3 ));

Select

B. Name table_name,

Case when C. Type = 0 then heap

When C. Type = 1 then aggregation

When C. Type = 2 then non-aggregation

Else others

End index_type,

C. Name index_name,

Pagepid, iampid, objectid, indexid, pagetype, indexlevel,

Nextpagepid, prevpagepid

From tablepage A, SYS. Objects B, SYS. Indexes C

Where a. objectid = B. object_id

And a. objectid = C. object_id

And a. indexid = C. index_id

-- Obtain the root page address of the table. The root node of the clustered index can be found only through the following script.

Select C. Name, A. type_desc, D. Name,

Total_pages, used_pages, data_pages,

Testdb. DBO. f_get_page (first_page) first_page_address,

Testdb. DBO. f_get_page (root_page) root_address,

Testdb. DBO. f_get_page (first_iam_page) iam_address

From SYS. system_internals_allocation_units A, SYS. partitions B, SYS. Objects C, SYS. Indexes d

Where a. container_id = B. partition_id and B. object_id = C. object_id

And D. object_id = B. object_id and D. index_id = B. index_id

And C. Name in (testheapindex)

-- In the following examples, the scripts for obtaining the relevant pages are basically the same as those for the root page, and are no longer repeated.
 

Unique non-clustered index on the heap table

 

First, heap tables are composed of several leaf pages, which have no links to each other and are managed and maintained entirely on the iam page.

We can see that page (1: 90) is the only root node of the non-clustered index, because there is actually only one index node, that is, the value greater than or equal to I1 points to the leaf page (1: 93). If the value is smaller than I1, it points to the leaf page.

The leaf page contains the index value of each index entry and the location of the record pointed to by the index (file number + Page + slot number ), because this is a unique index, no additional fields are required to record duplicate values.

Non-unique non-clustered index on the heap table

 

We can see that page (1: 94) is the root node of this non-unique non-clustered index, because there is actually only one index node for a small amount of data, that is, if the value is greater than or equal to G1, it points to the leaf page (). If the value is smaller than G1, it points to the leaf page.

The leaf page contains the index value of each index entry and the location of the record pointed to by the index (file number + Page + slot number ); the storage structure is exactly the same as the unique non-clustered index in the heap table. Although the index value is not unique in the heap table, the index value + pointer (file number + Page + slot number) to ensure the uniqueness of the index entry, so no additional auxiliary fields are required.

Unique clustered index table

Create Table testuniqueclusterindex

(

Name char (200 ),

Type1 char (900 ),

Type2 char (900)

)

Create unique clustered index idx_testuniqueclusterindex_cluster on testuniqueclusterindex (name)

Create unique index idx_testuniqueclusterindex1 on testuniqueclusterindex (type1)

Create index idx_testuniqueclusterindex2 on testuniqueclusterindex (type2)

Insert into testuniqueclusterindex values (A, a1, a2)

Insert into testuniqueclusterindex values (B, b1, b2)

Insert into testuniqueclusterindex values (C, C1, B2)

Insert into testuniqueclusterindex values (D, D1, B2)

Insert into testuniqueclusterindex values (E, E1, C2)

Insert into testuniqueclusterindex values (F, F1, F1)

Insert into testuniqueclusterindex values (G, G1, G1)

Insert into testuniqueclusterindex values (H, H1, G1)

Insert into testuniqueclusterindex values (I, i1, G1)

Insert into testuniqueclusterindex values (J, J1, J1)
 

Unique non-clustered index on the unique clustered index table

 

First, we can see that page (1: 192) is the root node of the clustered index. This root node contains two index values and three index pointers pointing to the corresponding leaf page, the leaf pages of clustered indexes are stored according to the sorting rules of clustered indexes.

Page (1: 194) is the root node of the unique non-clustered index on the unique clustered index table. Because the data volume is small, there is actually only one index node, that is, if the value is greater than or equal to H1, it points to the leaf page (). If the value is smaller than H1, it points to the leaf page.

The leaf pages of the unique non-clustered index include the index value and the clustered key value of each index entry. Because they are unique, the two can guarantee the uniqueness of the index entry, therefore, no additional auxiliary fields are required.

So how does one find the unique non-clustered index fields? SQL Server starts to check the root node of the unique non-clustered index until a suitable index leaf page is found. Then, based on the clustered index key value in the index entry