In database technology indexes used to improve the performance of select queries that can be seen from the following figure.
This post is to show case different cases for indexes using different articles. This article SQL Server Clustered Index Behavior Explained via Execution Plans will tell you how different scenarios are for Clustered Index.
However, indexes needed to be maintained using rebuild and reorganizing SQL Server Maintenance Plan Index Rebuild and Reorganize Tasks .
How do we know what is the better index and where the indexes are needed. This can be done Troubleshooting using Wait Stats in SQL Server and specially How to Avoid CXPACKETs? . Another place indexes are Internals of Physical Join Operators (Nested Loops Join, Hash Match Join & Merge Join) in SQL Server .
Column indexes are another important aspect and Script to Create and Update Missing SQL Server Columnstore Indexes.
Furhter we have discussed few more cases for index before at here
Happy reading into this.
