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