Indexes and maintenance

Indexes are sometimes seen as some magic objects that resolve all performance problems in the world of relational databases. Nothing could be further from the truth. In this section, we will look at the index types offered by SQL Server, and then discuss how indexes work. Finally, we will summarize some guidelines for using indexes.

Types of indexes

Sorting in relational databases makes no sense. Even though this statement sounds strange, it comes from the set theory, which forms a theoretical base of relational databases. However, sorting is still needed when the database engine must find proper records from a table efficiently. When no sorting structure is present, the engine needs to scan all the records to recognize which of them are candidates for the result of a certain query. An index is a type of object that brings sorting and seeking possibilities over unsorted relational data.

SQL Server provides several types of indexes...