An instance of SQL Server's index structure

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.