Sqlsever About Indexes

Index:

In SQL Server, the smallest unit of storage is the page and the page is non-divided
B-Tree: The original intention is to reduce the number of scans on the disk, if a table or index does not use a B-tree (for a table that does not have a clustered index is stored using the heap heap), then to find a data, the entire table contains the database page in the overall scan, which greatly increased the IO burden
Package SQL Server uses a B-tree for storage, only needs to store the B root node in memory, and after several lookups find the page that holds the required data on the child page of the node, thus avoiding a full scan, which improves performance

If there is no index in the table, it is stored as a heap,

The reduction of IO can be demonstrated by adding a clustered index (stored in B-tree) to it

For example:

--* FROM student-- to create a clustered index ona table--5

Aggregation: In SQL Server: The role of aggregation is to change the physical order of one or more columns to match the logical order

The clustered index changes the physical storage order of the table in which it resides, so there can be only one clustered index per table

In SQL Server: The storage of a clustered index is stored as a B-tree, and the leaves of the B-tree store the data of the clustered index directly
Nonclustered indexes: Instead of altering the physical structure of the table in which they are located, an additional B-tree structure for a clustered index is generated, but the leaf node is a reference to its table.
This reference is divided into two types: if the table does not have a clustered index, the row number is referenced, and if the table already has a clustered index, the page referencing the clustered index

Nonclustered indexes require additional space to be stored, clustered by indexed columns, and a leaf node in the B-tree contains a pointer to the table of the nonclustered index

Unlike a clustered index, a leaf node in a B-tree is a pointer to a heap or clustered index

A nonclustered index contains only columns from an nonclustered index in the original table and pointers to the actual physical table.

If the physical structure of a table changes, such as adding or removing a clustered index, all nonclustered indexes need to be rebuilt, which is quite a significant loss for performance, so it is best to build a clustered index and then set up the corresponding nonclustered index

In most cases, a clustered index is slightly faster than a nonclustered index because the B-tree leaf nodes of the clustered index store data directly, and non-clustered indexes require additional data to be found through the leaf node's pointer

Also: Nonclustered indexes perform poorly for large numbers of continuous data lookups because nonclustered indexes need to find each row's pointer in the B-Tree of the nonclustered index, and then go to the table where the data is found, and the performance is compromised, rather than the nonclustered index

Therefore, in most cases, the clustered index is faster than the nonclustered index, but there is only one clustered index, so be sure to choose which or which columns to use as the clustered index

Use of indexes:
The use of indexes does not need to be used explicitly, and the Query Analyzer automatically finds the shortest Path usage index after indexing

But as the volume of data grows, index fragmentation occurs, and many of the stored data is not properly paginated, causing fragmentation (spreads, fragments, fill factors) that need to be re-indexed to speed performance

The DMV statement can be used to query its index condition

SELECT Index_type_desc,alloc_unit_type_desc,avg_fragmentation_in_percent,fragment_count,avg_fragment_size_in_ Pages,page_count,record_count,avg_page_space_used_in_percent
From Sys.dm_db_index_physical_stats (db_id (' yip_20160322 '), object_id (' student '), Null,null, ' Sampled ')

When the amount of debris exceeds 40% (view this column: Avg_fragmentation_in_percent), you often need to rebuild the index, which reduces the IO

You can rebuild the index like this:
ALTER INDEX idx_student_id on student REBUILD

and UPDATE STATISTICS for tables

UPDATE STATISTICS Student

The cost of using the index:

1 when the table is indexed, the data is stored as a B-tree, so when the update is inserted and deleted, it is necessary to move the page physically to adjust the B-tree, thus causing performance degradation.
2 for nonclustered indexes, when the table is updated, the nonclustered indexes also need to be updated, which is equivalent to a multiple update of N (number of n= nonclustered indexes) tables, thus also dropping performance

3 You can usually place a nonclustered index on a separate hard disk so that you can scatter IO so that the query can be parallel

Sqlsever About Indexes