SQL Server Performance Optimization (12) composite index storage structure for nonclustered indexes

One, nonclustered index combined index

Users can index on multiple columns, which are called composite indexes (combined indexes). However, composite indexes require less overhead during database operations and can replace multiple single indexes. Using this method can significantly speed up the query speed of a table when the number of rows in the table is much larger than the number of index keys.
At the same time, there are two concepts called Narrow Index and wide index, narrow index refers to index column 1-2 index, if not special description is generally referred to as a single index. A wide index is an index that has more than 2 columns in the index column.
An important principle of designing indexes is the ability to use narrow indexes without wide indexes, because narrow indexes tend to be more efficient than composite indexes. Having more narrow indexes will give the optimizer more room to choose from, which often helps improve performance.

/****** Object:index [Nonixuser] Script date:05/25/2015 09:03:01 ******/
CREATE nonclustered INDEX [nonixuser] on [dbo]. [Users]
(
[NAME] Asc
[ID] ASC
)
GO

or in the GUI.

Two, the root node structure of the combined index

First look at the physical structure of the table

--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)

Viewing the contents of the first index leaf node

DBCC page (ixtest,1,201,3)

Note:

1. when a composite index query does not contain columns, the RID query is still gone.

2. If it is an include index, it will also be in the index leaf node more relevant columns, but its column header does not contain (key) this keyword. As follows

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 (12) composite index storage structure for nonclustered indexes