SQL Server review-Index

Index cost

1. Added, changed, and deleted indexes are maintained.

2. Indexes occupy a certain amount of physical space

 

Indexing principles

1. The primary key must be indexed

2. Foreign keys must be indexed

3. It is best to create an index for frequently queried data.

A. You can use index sorting to accelerate the query time for range data.

B. Create an index in the set of where clauses

4. Do not create indexes for columns that are rarely involved in queries and columns with multiple duplicate values.

5. Do not create indexes for columns with large or small data volumes.

 

Index category

1. Clustered index (physical sorting) (only one)

2. Non-clustered index (no physical sorting) (249)

3. Unique index (unique constraint on data)

 

Index Analysis

1. showplan_all (display query plan)

Demo: Set showplan_all on

2. Statistics io (retrieve disk activity volume)

Demo: Set statistics Io on

 

Index Maintenance

Over time, due to a series of operations such as addition, deletion, and modification, data becomes disordered. Index maintenance required

1. Statistics Update

Update statistics class pk_class

2. Scan table information

DBCC showcontig (class, pk_class)

Note: The scan density of 100% is an ideal value. If the scan density is too low, the table fragments need to be cleared.

3. Fragment

DBCC indexdefrag (sukinshow, class, pk_class)