index--index from the point of view of data storage

Test table structure:

CREATE TABLE TB1 (    intIDENTITY(1,1),    int,     INT )

1. Clustered indexes (Clustered index)

A clustered index can be understood as a contained index that contains more columns than the index key in a table, to ensure correctness in the delete/update operation, and if the clustered index is not declared as unique (unique), the system aggregates the index key by adding an nullable int type identity column ( Uniquifier) to ensure record uniqueness.

Unique Clustered index:

CREATE UNIQUE CLUSTERED INDEX idx_id  on TB1 (    ID)

Non-unique clustered index:

CREATE CLUSTERED INDEX idx_id  on TB1 (    ID)

2. Non-unique nonclustered indexes

To locate data from a nonclustered index, the nonclustered index holds the index key + data RID (file_id+page_id+slot_id) for the heap table, and for clustered tables, the nonclustered index holds the index key + clustered index key.

Nonclustered indexes:

CREATE INDEX IDX_C1  on TB1 (    C1)

Nonclustered indexes on heap tables:

Nonclustered indexes on unique clustered index tables:

Non-unique clustered Index Table nonclustered index:

--==============================

Subsequent tests use a unique clustered index by default

--==============================

3. Unique Nonclustered index

The difference between a unique nonclustered index and a non-unique nonclustered index is primarily on non-leaf nodes, and the non-leaf nodes of a unique nonclustered index do not contain data on rids.

Unique Nonclustered indexes:

CREATE UNIQUE INDEX Idx_c1_uni  on TB1 (    C1)

4. Include index

The include index was introduced in the SQL SERVER 2008 version, and the data that contains the columns exists only on the leaf nodes. The containing column does not affect the position of the index row (it is not sorted), and the containing column does not affect the size of the index key (SQL SERVER restricts the index key to be 900 bytes)

CREATE INDEX IDX_C1_INC_C2  on TB1 (    C1) INCLUDE (    C2)

5. Filtering the Index

When a filtered column is not used as an index key or contains a column, the system does not need to store the filtered column data in the index, so the filtered column does not appear on the leaf and non-leaf nodes of the index.

CREATE INDEX IDX_C1_WH_C2  on TB1 (    C1)WHERE C2>1

--=============================================================

Summary & Recommendations:

1. For clustered tables, because index nonclustered indexes contain clustered keys, it is recommended that static + unique + increment + length-less index keys be preferred as index keys

A. Static: When the clustered key is updated, in addition to moving the table data to the appropriate location, updating all nonclustered indexes in turn consumes a lot of resources and causes page splits and index fragmentation

B. Unique: A non-unique clustered index increases the consumption of 2 to 6 bytes, resulting in more pages consumed by clustered and nonclustered indexes

C. Increment: For non-incrementing clustered index keys, insert operations cause page splits and index fragmentation

D. Small length: A large clustered index key can also cause both clustered and nonclustered indexes to consume more pages, especially to increase the number of index layers and increase the cost of index seek.

2. Optional and indexed columns of indexed columns Shun: High selectivity does not mean that the column fits in the front of the index, but should also consider whether the column is a range query or an equivalent query, such as the creation time column of the order table Createdtime is primarily used as a range query. and the order form product number ProductID mainly with the equivalent query, then for

WHERE ProductID=@P1and        createdtime>@P2 and Createdtime<@p3 　　　　

For such queries, index (PRODUCTID,CREATEDTIME) is more efficient than index (CREATEDTIME,PRODUCTID) (consumes less CPU and IO resources).

3. Index column order and statistics: The index column is different in order, its statistical information on the density (density) and histogram (histogram) is not the same, will indirectly affect the generated execution plan.

4. For columns that have a lower selectivity and are located at the back end of the index column, consider placing them in the containing index column.

5. Although the filtering index has some problems in the updating of statistics, the filtering index is still a must kill technique to solve some incurable diseases (such as select TOP * from Orders where productid>10000 ORDER by OrderID DES C

6. When indexing an incrementing column, consider the issue of inefficient execution of the statistics due to the expiration of the statistic, such as indexing the created Date column on the Order table.

--==========================================================

Not written well, by sister plus points.

index--index from the point of view of data storage