Indexes and maintenance

Indexes are sometimes seen as some kind of magic objects that resolve all performance problems in the world of relational databases. Nothing is as far from the truth than this opinion. In this section, we will go through index types offered by SQL Server, then discuss how indexes cooperate together and 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 that forms a theoretical base of relational databases. However, sorting is still needed when the database engine has to find out proper records from a table efficiently. When no sorting structure is present, the engine needs to scan over all records to recognize which of them are a candidates for the result of a certain query. An index is a type of object bringing sorting and seeking possibilities over unsorted relational data.

SQL Server provides several types of indexes. The most traditional...