Jinwan Platform SQL Server index principle in-depth analysis buckle 892118

1.1 Clustered Index
The data for the table is stored in the data page (the pagetype tag of the data page is 1), and the SQL Server page is 8k, and the next page is saved by filling a page. If the table has a clustered index, then a single piece of physical data is stored in the page by the size of the clustered index field in ascending/descending order. When you update or intermediate insert/delete data on a clustered index field, it causes the table data to move (which can have a performance impact) because it keeps ascending/descending ordering.

Note that the primary key is simply the clustered index by default, it can also be set to a nonclustered index, or it can be set to a clustered index on a non-primary key field, and only one clustered index is available for the whole table.
A good clustered index field typically contains the following 4 features:
(A). Self-growth
Always add records at the end to reduce paging and index fragmentation.
(B). Not to be changed
Reduce data movement.
(C) Uniqueness
Uniqueness is the most desirable attribute of any index and can be used to clarify the position of the index key value in the sort.
More importantly, the index key is unique, and it can point to the source data row RIDs correctly in each record. If the clustered index key value is not unique, SQL Server needs an internally generated Uniquifier column combination as a clustered key to guarantee the "key value" uniqueness; if the nonclustered index key value is not unique, the RID column (the clustered index key or the row pointer in the heap table) is added to guarantee the "key value" uniqueness.
Thinking (can be skipped): The index "key value" is also guaranteed to be unique on non-leaf nodes, because it should be to clarify the position of the index record in a non-leaf node. For example, there is a nonclustered index field Name2, there are many records of name2= ' a ' in the table, causing name2= ' a ' to have multiple index records (nodes) on a non-leaf node, and then insert a record of name2= ' a '. It is possible to quickly determine which index record (node) to insert on the rid of the non-leaf node and the rid of the new record, and if there is no rid of the non-leaf node, it is necessary to traverse the leaf nodes of all name2= ' a ' to determine the location. Also, when we select * from Table1 where name2<= ' a ', the returned data is sorted by nonclustered index Name2 and RID, and it is good to understand that the returned data is sorted in the order in which the index is stored here. This is the result of this SQL query that is useful to the Name2 index, and if the database query plan chooses direct table data scanning due to a "tipping point" problem, the returned data is sorted by default in the order of the table data.
For the key value uniqueness, for clustered indexes, the Uniquifier column is incremented only when the index value repeats. For nonclustered indexes, if the index is created without a unique definition, the RIDs are incremented at all records, even if the index value is unique, and if the index is created uniquely, the RID is only added to the leaf layer to find the source data row, which is the bookmark lookup operation.
(D). Small field length
The smaller the clustered index key length, the more index records can be accommodated by one page of index pages, thus reducing the depth of the index B-tree structure. For example, a table with millions of records has an int clustered index, which may only require a 3-tier B-tree structure. If you define a clustered index in a wider column (for example, the uniqueidentifier column requires 16 bytes), the depth of the index is increased to 4 levels. Any clustered index lookup requires 4 I/O operations (exactly 4 logical reads), originally as long as 3 I/O operations.
Similarly, the nonclustered index contains the clustered index key value, the smaller the clustered index key length, the smaller the non-clustered index record, and the One-page index page can accommodate more index records.

Jinwan Platform SQL Server index principle in-depth analysis buckle 892118