Chapter 41. Speeding up your queries with index covering
When a nonclustered index contains all the columns from one table needed to satisfy a query, that index is called covering with respect to that query, and that query is covered by that index. In many cases, creating a covering index is a quick and easy way to boost the performance of a query. But covering indexes aren’t free—they use additional storage, slow down most updates, and may cause concurrency issues such as lock contention and deadlocks. This means that speeding up some selects may and usually will cause some modifications to run slower. Usually, you don’t need to optimize standalone queries; in most cases, you should aim to improve the system’s overall productivity. To accomplish that, you need to find some balance to provide a system with selects that run faster and updates that still perform reasonably well. You’ll see several examples demonstrating these concepts in this chapter. The first example will demonstrate how a covering index speeds up selects.