Non-clustered index of SQL Server 2008 storage structure

Nonclustered indexes have the same B-tree structure as clustered indexes, and the significant difference between them is the following two points:

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

The leaf layer of a nonclustered index is made up of index pages rather than data pages.

Nonclustered indexes can be built either on a heap table structure or on a clustered index table; Each index row in a nonclustered index contains nonclustered key values and row locators. This locator points to the data row that contains the key value in the clustered index or heap.

If the table is a heap, the row locator is a pointer to the row. The pointer is generated by the file identifier (ID), the page number, and the number of rows on the page. The entire pointer is called a row ID (RID).

If the table contains a clustered index, the row locator is the clustered index key for the row. If the clustered index is not a unique index, SQL Server adds internally generated values, called unique values, to make all duplicate keys unique. This four-byte value is not visible to the user. This value is added only if you need to make the clustered key unique for use in a nonclustered index. SQL Server retrieves rows of data by searching for a clustered index by using a clustered index key that is stored in a leaf row of a nonclustered index.

The page collection of the B-tree is positioned by the root_page pointer in the sys.system_internals_allocation_units system view.

Heap Table

--Creates a heap table create table Testheapindex (name CHAR (), Type1 char (900), type2 char (9  00)--Create a unique index and a non-unique index created uniquely indexed idx_testheapindex1 on Testheapindex (type1) CREATE index  Idx_testheapindex2 on Testheapindex (type2)--inserting test data insert into Testheapindex VALUES (' A ', ' A1 ', ' A2 ') insert into Testheapindex values (' B ', ' B1 ', ' B2 ') inserts into Testheapindex values (' C ', ' C1 ', ' B2 ') inserts into Testheapin Dex values (' D ', ' D1 ', ' B2 ') insert into testheapindex values (' E ', ' E1 ', ' C2 ') inserts into Testheapindex values (' F ', ' F1 ', ' F1 ') insert into testheapindex values (' G ', ' G1 ', ' G1 ') inserts into Testheapindex values (' H ', ' H1 ', ' G1 ') I
    
Nsert into Testheapindex values (' I ', ' I1 ', ' G1 ') inserts into Testheapindex values (' J ', ' J1 ', ' J1 ')--Get the appropriate page information for 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, when c.type=0 THEN ' heap ' when c.type=1 ' aggregation ' when C . type=2 THEN ' nonclustered ' else ' other ' end Index_type, C.name index_name, Pagepid,iampid,objectid, Indexid,pagetype,indexlevel, nextpagepid,prevpagepid from Tablepage a,sys.objects b,sys.indexes c WHER E a.objectid=b.object_id and a.objectid=c.object_id and a.indexid=c.index_id-Gets the root page address of the table, the root node of the clustered index SELECT C.name,a.type_desc,d.name, Total_pages,used_pages,data_pages, testdb.dbo must be found through the following script. F_get_page (first_page) first_page_address, Testdb.dbo.f_get_page (root_page) root_address, TESTDB.D Bo.f_get_Page (first_iam_page) iam_address from sys.system_internals_allocation_units a,sys.partitions b,sys.objects c,sys.in Dexes d WHERE a.container_id=b.partition_id and b.object_id=c.object_id and d.object_id=b.object_id and D.I NDEX_ID=B.INDEX_ID and C.name in (' Testheapindex ')--The following example gets the script for the related page and root page basically the same, no more repeating