A covering index contains the columns needed to filter, join, and return a query’s results, avoiding a lookup of the base table. A common mistake is using SELECT *: the index must then contain every returned column, becoming wide, inefficient, and fragile when the table changes. section 5.3
Use covering indexes deliberately, and check their costs:
- Put filtering or ordering columns in the index key; place the small set of additional returned columns in INCLUDE at the leaf level. section 5.3 section 5.4
- Avoid adding every table column merely to support SELECT *; such an index can stop covering the query after a schema change. section 5.3
- Treat the index as a trade-off: it can remove lookups and sort work, but INSERT, UPDATE, and DELETE operations must maintain it. section 5.4
Listing 5.6 Creating a covering index
CREATE NONCLUSTERED INDEX [OrderDate-Including-DueDate-ActualDate] ON dbo.SalesOrderHeaders (SalesOrderDate) INCLUDE(SalesOrderDeliveryDueDate,SalesOrderDeliveryActualDate) ;
Create a nonclustered index keyed by SalesOrderDate and include the two delivery-date columns required by the query. section 5.3
Listing 5.10 Creating a covering index to improve performance
CREATE NONCLUSTERED INDEX
[NI-CustomerContactEmail-Include-FirstName-LastName]
ON dbo.CustomerContacts(CustomerContactEmail)
INCLUDE(CustomerContactFirstName, CustomerContactLastName) ;
Cover a query ordered by customer email by using email as the key and including first and last names, avoiding a sort and clustered-index lookup. section 5.4
