Indexing for T-SQL Performance

In the previous chapter, we explored execution plans and the various operators that the SQL Database Engine uses to retrieve the data requested by a query. While the Query Optimizer does most of the heavy lifting when choosing the best way to retrieve the data required to satisfy the query, it can only do so efficiently if the proper indexes are in place.

An index is a structure in the database that speeds up access to data by organizing it in a specific way based on the type of index. The data structure that works best for your application will depend on many factors, including the type of data being stored, the volatility of the data, and the data access patterns that will be used to retrieve the data. The SQL Database Engine offers a few different index types, such as rowstore, columnstore, XML, and others. Rowstore indexes are the most common indexes in the SQL Database Engine and are what most people think of when considering indexes for query...