Often in applications, we write database queries. And often, the same query is executed again and again. To make queries against large tables faster, we can add an index. This is a general improvement for anyone accessing the table. However, there is a specially designed index called a covering index that can make queries against large tables particularly faster for one specific query. We call it covering because it covers all the projections and predicates in that query.
Which query? That is up to you, the developer, to identify. Look for queries that are either 1) very important to run fast or 2) extremely frequent in your application code. Just remember, indexes are not free, so we do not create one for every query. But for the right query, we can justify a covering index, and it can make all the difference.
A little deeper
Imagine an application that shows a list of customer orders. It would frequently run this query:
SELECT
OrderDate,
TotalAmount,
Status
FROM dbo.Orders
WHERE CustomerId = 42;
Let’s look at how SQL Server handles a query like this. Once the engine identifies the correct table, it applies the predicates in the WHERE clause to filter the rows. In this case, it keeps only rows where CustomerId is 42. This can reduce millions of rows, and tons of unnecessary data, to just the required few.
Finally, SQL Server applies the projection in the SELECT clause to return only OrderDate, TotalAmount, and Status. Notice that CustomerId is needed to find the rows but is not part of the final column list. This can reduce hundreds of columns, and tons of unnecessary data, to just the required few.
The mechanics of filtering rows
To make this fast, SQL Server looks for a useful index. Without one, the engine may have to step through every row in the table. With one, SQL Server can seek directly to the matching values instead of scanning the entire table. Think about finding a specific book in a large library without knowing where it is versus having its index location. It is night and day, even for a fast engine like SQL Server.
The clustered index
CREATE CLUSTERED INDEX IX_Orders_OrderId
ON dbo.Orders(OrderId);
Tables with a primary key usually have an index supporting that key. By default, SQL Server creates it as a clustered index unless the table already has one or you specify otherwise.
Imagine that same library with all its books mixed up. A clustered index is like arranging them in order, making it faster to locate a specific section. However, this is the Orders table, so its primary key is likely OrderId, not CustomerId. The primary-key index therefore does not directly help SQL Server find every order for customer 42.
The nonclustered index
CREATE NONCLUSTERED INDEX IX_Orders_CustomerId
ON dbo.Orders(CustomerId);
We could create a nonclustered index on CustomerId. Think of Battleship. When someone says E-4, you know exactly where to look. A nonclustered index works similarly: it identifies where matching rows can be found without reorganizing the entire table. It is also worth pointing out that CustomerId being a foreign key does not mean it automatically has an index. A foreign key references a key in another table, in this case Customers, but SQL Server does not automatically create an index for it.
The worst case
Without an index on CustomerId, SQL Server does what it has to do: it scans the table, tests each row, and keeps the matching ones. With a few thousand rows, this may still be fast. With a few million, it can become noticeably slow. All of this may happen in a second or so. A covering index can turn that into only a few milliseconds.
The covering index
Clustered or nonclustered is not the important part of a covering index. The query is. More specifically, the predicates in the query and the columns projected by the query are what matter. A query may benefit generically from an existing index, but a covering index is intentionally designed to match that query’s predicates and projected columns.
Including columns
CREATE NONCLUSTERED INDEX IX_Orders_CustomerId
ON dbo.Orders(CustomerId)
INCLUDE (OrderDate, TotalAmount, Status);
Now is an important time to mention that indexes can contain more than predicate columns. They can also include data.
The INCLUDE keyword adds columns to the index without making them part of the index key. If SQL Server finds the predicate value in the index and the query requests only columns included in that index, it may not need to return to the source table at all.
This combination of matching predicates and available projected columns is the heart of a covering index. Nothing could be faster than finding matching predicates in an index and, while already there, returning the included columns. This is one of the quickest ways to return query results.
Designing a covering index
To design a covering index, start with the query. Identify the columns used in the predicates, then identify the columns returned by the projection. Let’s go back to our original query:
SELECT
OrderDate,
TotalAmount,
Status
FROM dbo.Orders
WHERE CustomerId = 42;
Once you determine the hot spots in your application, the query or queries that get the most exercise, it is worth testing their performance against production-sized tables. A frequent query is not always an immediate candidate for a covering index. However, if you determine that one of these hot-path queries is impacting the user experience, it is time to think about a covering index.
Step 1: Identify the predicates
A lot of custom code dynamically adds or removes WHERE predicates through a filter interface or based on user behavior. Identify either the superset of columns used as predicates or the columns that appear in your core use cases.
In our sample, this is only CustomerId. Here is the syntax for adding that predicate to the index:
CREATE NONCLUSTERED INDEX IX_Orders_CustomerId
ON dbo.Orders(CustomerId);
The columns inside the parentheses are the index key columns. They are ordered and used by SQL Server to find matching rows.
Column order matters too.
Put equality predicates first, followed by range predicates, because SQL Server uses the leading index columns to narrow the search. Included columns do not affect the sort order, so their order is usually less important. Projection columns order usually does not matter.
Step 2: Identify the data
Pay attention when duplicating table data into an index, especially when the column data types are large. Columns containing JSON, vectors, or binary data should cause you to reconsider this step.
But perhaps the projected columns are only OrderDate, TotalAmount, and Status, as in our sample query. If you are comfortable with the additional storage, or the user experience is paramount in this equation, include all the projected columns.
Two caveats are important.
If you include all but one projected column in the index, SQL Server may still be required to return to the source table for that missing value. This eliminates much of the core value of a covering index.
On the flip side, if you include every column in the table, you have nearly duplicated the source table and introduced unnecessary storage and maintenance costs.
Here is the syntax for including the projected columns:
CREATE NONCLUSTERED INDEX IX_Orders_CustomerId
ON dbo.Orders(CustomerId)
INCLUDE (OrderDate, TotalAmount, Status);
Indexes maintain themselves. Once an index is initially built, changes to the underlying table automatically update that index and every other affected index on the table. It is worth remembering that an insert, update, or delete may not complete until the affected indexes are also updated. This means large or excessive indexes can impact the OLTP performance of your database.
Conclusion
A covering index is a kind of secret sauce when it comes to SQL performance. That is, for some queries. An army of covering indexes should be a code smell, for sure.
For the rest of your database and other queries, it often makes more sense to create general indexes that serve several queries. In fact, the automatic tuning feature in Azure SQL reviews query activity to identify queries that might benefit from additional indexes or changes to existing indexes. You can do the same thing.
Do not just add a covering index.
Test it to ensure that unwanted side effects do not creep into your workload.
In either case, the key to database performance is a smart schema, smart queries, and appropriate indexes. Think through your application and the queries you run. The next time you find a hot spot, open Copilot in SQL Server Management Studio, paste your query, and prompt:
Help me design a covering index for this query.
Then see what you can do.
The post T-SQL Hygiene: Introducing the Covering Index appeared first on Azure SQL Dev Corner.
Log in
