Nonclustered indexes and clustered indexes

The fundamental difference between a clustered index and a nonclustered index is whether the table records are arranged in the same order as the index, clustered index table records are arranged in the same order as the index, and the advantage is that the query is fast because once a record with the first index value is found, the record with the continuous index value is physically followed. The disadvantage of a clustered index is that it is slower to modify the table in order to maintain the physical order of the records in the table in the same order as the index, while inserting the records into the corresponding locations in the data pages, the data page must be rearrangement and the execution speed reduced. The recommended scenarios for using clustered indexes are:
A. This column contains a limited number of different values;
B. The result of the query returns the value of an interval;
C. The result of a query returns a large number of result sets of the same value.
A nonclustered index specifies the logical order of records in a table, but the physical order of the records is inconsistent with the order of the indexes. Both clustered and nonclustered indexes use the B + tree structure, but the leaf layer of the nonclustered index does not overlap with the actual data page, and the leaf layer contains a pointer to the record in the table in the data page. Nonclustered indexes are more hierarchical than clustered indexes, and adding records does not result in a reorganization of the data order. The recommended scenarios for using nonclustered indexes are:
A. This column contains a large number of different values;
B. The end of the query returns a small number of result sets;
The column is used in the C.order by clause.



--No indexing query
SELECT * from Indextesttable with (INDEX (0))
WHERE status= ' B '

--Create a clustered index
CREATE CLUSTERED INDEX icindextesttable
On Indextesttable (Status)
Go

--Using index queries
SELECT * from Indextesttable with (INDEX (icindextesttable))
WHERE status= ' B '

The following table summarizes when to use clustered or nonclustered indexes.

Should

Action Description	use clustered index	Use a nonclustered index
Column is often sorted	Should	should
Returns a range of data	Should	should not
One or very few Different values	Should not be	should not
Small number of different values	should be	Do not
Large number of different values	should not	should
Frequently updated columns	No	Should
Foreign key columns	Should	Should
Primary key columns	should
Frequently modify index columns	should not	Should

　　In fact, we can understand the table above by using examples of the definitions of the previous clustered and nonclustered indexes. For example, returns a range of data items. For example, if you have a table with a time column that happens when you set up the aggregate index in that column, this speed will be quick when you query all the data from January 1, 2004 to October 1, 2004, because the text of your dictionary is sorted by date, The clustering index only needs to find the beginning and end data in all the data to be retrieved, and unlike nonclustered indexes, you must first look up the page number of each item in the table of contents, and then find the specific content based on the page number.