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.

1234567891011

USE Test--1.创建表，指定主键(会自动创建聚集索引)CREATETABLEPerson(Id intNOTNULLIDENTITY,Namevarchar(10) NOTNULL,Sex varchar(2) NOTNULL,CONSTRAINT PK_Person PRIMARYKEY(Id));--2.创建非聚集索引CREATEINDEXidx_Person_Sex ONPerson(Sex);

1234	--3.插入1笔数据InsertPerson values(‘P0‘,‘M‘);--4.查看表有哪些页DBCC ind ( Test, [dbo.Person], -1)

1234	--5. 打开3604监控DBCC TraceON(3604,-1)--6.查看聚集索引叶子节点页的数据DBCC PAGE (Test,1,174, 1);

12345678910111213141516171819202122

--7.插入1000条M和500条W记录SETNOCOUNT ON;GODECLARE@i int;SET@i = 1000;WHILE @i < 2000BEGINInsert Person values(‘P‘+ convert(varchar(10),@i),‘M‘);SET@i = @i + 1;END; DECLARE@i int;SET@i = 2000;WHILE @i < 2500BEGINInsertPerson values(‘P‘+ convert(varchar(10),@i),‘W‘);SET@i = @i + 1;END;GO --8.查看表有哪些页DBCC ind ( Test, [dbo.Person], -1)

12	--9.查看聚集索引页的数据DBCC PAGE (Test,1,209, 3);

12	--10.查看非聚集索引页的数据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