An instance of SQL Server's index structure

The current index structure for SQL Server is as follows:

This is the storage form of the clustered index:

Nonclustered indexes are in the following ways:

They are stored in the data structure of a B + tree.

I believe we have seen similar graphs, but there is no intuitive understanding, the following is a practical example to illustrate the structure of the graph.

Use test--1. CREATE TABLE, specify primary key (the clustered index is automatically created) CREATE TABLE person (Id int. not NULL identity,name varchar () not null,sex varchar (2) Not N Ull,constraint Pk_person PRIMARY KEY (Id))--2. Creating a nonclustered index create index idx_person_sex on person (SEX);

--3. Inserting 1 Data Insert person values (' P0 ', ' M ');--4. View what pages the table has DBCC IND (TEST, [dbo]. Person],-1)

--5. Open 3604 Monitor DBCC TraceON (3604,-1)--6. View clustered index leaf node page data dbcc page (test,1,174, 1);

--7. Insert 1000 m and 500 W record set NOCOUNT on; Godeclare @i int; SET @i = 1000; While @i < 2000BEGINInsert person values (' P ' + convert (varchar), @i), ' M '); SET @i = @i + 1; END; DECLARE @i int; SET @i = 2000; While @i < 2500BEGINInsert person values (' P ' + convert (varchar), @i), ' W '); SET @i = @i + 1; END; GO--8. View what pages the table has DBCC IND (TEST, [dbo]. Person],-1)

--9. Viewing data for a clustered Index page DBCC page (test,1,209, 3);

--10. Viewing data for nonclustered index pages DBCC page (test,1,189, 3);

Summary of Index:

An index can have multiple page

Index is stored in a B + tree structure, where the information of the branch node is present in a page, and the leaf node is stored in the other page.

An instance of SQL Server's index structure