SQL Server Overwrite Index

An overriding index is usually a composite index, which is the number of indexed fields. you should be aware of the index order when creating an index.

When SQL Server retrieves the application index, the field recognition order is left-to-right.

For example, the following index is used

Create nonclustered Index Ix_student_name_class_sex

On Tb_student (Name,class,sex)

Select * from Tb_student as S where s.name= ' ws '//Can be used

Select * from Tb_student as S where s.name= ' ws ' and S.class=1//can be used

Select * from Tb_student as S where s.name= ' ws ' and S.class=1 and s.sex=1//can be used

Select * from Tb_student as S where S.class=1 and s.sex=1//cannot use the index created

So when we create and use indexes, we need to be aware of the sort order of the fields.

When creating an index, use fields that are not updated as much as possible to avoid the non-clustered index maintenance costs that are brought when the update occurs. When you write an UPDATE statement, try to avoid updating the index fields.

You should decide if you need to update the field by adding a judgment so that you avoid the same data and update it, resulting in the cost of index maintenance for unnecessary data coverage.

If the index field must be updated frequently, then read-write separation can be used. The index is established in the query library as usual, and the update operates in the write library, avoiding the insertion of data maintenance indexes causing too many waits and deadlocks.

SQL Server Overwrite Index