SQL Server Performance Optimization (11) Overlay index storage structure for nonclustered indexes

One, include for nonclustered indexes

The Include property of a nonclustered index allows a nonclustered index to contain additional columns. Such as

CREATE nonclustered INDEX [nonixuser] on [dbo]. [Users]
(
[NAME] Asc
)
INCLUDE ([ID], [creattime]) GO

This table statement adds the ID on the nonclustered index of the name column, and the Createtime column.

In the previous introduction, we know that when querying name = ' 3,180 ', the RIDs will appear, while the number of reads is 3 times

Now add the Include column of the nonclustered index, what will the result be?

Iii. where did the RID query go?

1. View the root index page

--Turn on trace flag
DBCC TRACEON (3604,2588)
--DBCC Traceoff (3604,2588)
---Gets the data page of the object, structure: Database, object, display
DBCC IND (ixtest,users,-1)

Why is there a more index page?

Take a look at the first sub-index page

DBCC page (ixtest,1,175,3)

Reference:

Description of the limit on the number of column columns included on MSDN: https://technet.microsoft.com/zh-cn/library/ms191241 (v=sql.105). aspx

SQL Server Performance Optimization (11) Overlay index storage structure for nonclustered indexes