![\"covering](\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2026\/07\/covering-index.webp\")
<\/a><\/p>\nWhich query?<\/strong> 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.\u00a0Just 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.<\/p>\n Imagine an application that shows a list of customer orders. It would frequently run this query:<\/p>\n Let\u2019s look at how SQL Server handles a query like this. Once the engine identifies the correct table, it applies the predicates in the Finally, SQL Server applies the projection in the 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.\u00a0Think 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.<\/p>\n 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.<\/p>\n 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.\u00a0However, this is the We could create a nonclustered index on Without an index on 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.\u00a0A query may benefit generically from an existing index, but a covering index is intentionally designed to match that query\u2019s predicates and projected columns.<\/p>\n Now is an important time to mention that indexes can contain more than predicate columns. They can also include data.<\/p>\n The 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.<\/p>\n 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\u2019s go back to our original query:<\/p>\n 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.<\/p>\n A lot of custom code dynamically adds or removes In our sample, this is only The columns inside the parentheses are the index key columns. They are ordered and used by SQL Server to find matching rows.<\/p>\n <\/i>Column order matters too. <\/strong><\/p>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.<\/div><\/p>\n 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.<\/p>\n But perhaps the projected columns are only <\/i>Two caveats are important. <\/strong><\/p>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.<\/div><\/p>\n 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.<\/p>\n Here is the syntax for including the projected columns:<\/p>\n 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.\u00a0It 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.<\/p>\n 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.<\/p>\n 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.<\/p>\n <\/i>Do not just add a covering index. <\/strong><\/p>Test it to ensure that unwanted side effects do not creep into your workload.<\/div><\/p>\nA little deeper<\/h2>\n
SELECT\r\n OrderDate,\r\n TotalAmount,\r\n Status\r\nFROM dbo.Orders\r\nWHERE CustomerId = 42;\r\n<\/code><\/pre>\nWHERE<\/code> clause to filter the rows. In this case, it keeps only rows where CustomerId<\/code> is 42. This can reduce millions of rows, and tons of unnecessary data, to just the required few.<\/p>\nSELECT<\/code> clause to return only OrderDate<\/code>, TotalAmount<\/code>, and Status<\/code>. Notice that CustomerId<\/code> 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.<\/p>\nThe mechanics of filtering rows<\/h2>\n
The clustered index<\/h3>\n
CREATE CLUSTERED INDEX IX_Orders_OrderId\r\nON dbo.Orders(OrderId);<\/code><\/pre>\nOrders<\/code> table, so its primary key is likely OrderId<\/code>, not CustomerId<\/code>. The primary-key index therefore does not directly help SQL Server find every order for customer 42.<\/p>\nThe nonclustered index<\/h3>\n
CREATE NONCLUSTERED INDEX IX_Orders_CustomerId\r\nON dbo.Orders(CustomerId);<\/code><\/pre>\nCustomerId<\/code>. 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.\u00a0It is also worth pointing out that CustomerId<\/code> 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<\/code>, but SQL Server does not automatically create an index for it.<\/p>\nThe worst case<\/h3>\n
CustomerId<\/code>, 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.\u00a0All of this may happen in a second or so. A covering index can turn that into only a few milliseconds.<\/p>\nThe covering index<\/h2>\n
Including columns<\/h3>\n
CREATE NONCLUSTERED INDEX IX_Orders_CustomerId\r\nON dbo.Orders(CustomerId)\r\nINCLUDE (OrderDate, TotalAmount, Status);\r\n<\/code><\/pre>\nINCLUDE<\/code> 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.<\/p>\nDesigning a covering index<\/h2>\n
SELECT \r\n OrderDate, \r\n TotalAmount, \r\n Status \r\nFROM dbo.Orders \r\nWHERE CustomerId = 42;\r\n<\/code><\/pre>\nStep 1: Identify the predicates<\/h3>\n
WHERE<\/code> 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.<\/p>\nCustomerId<\/code>. Here is the syntax for adding that predicate to the index:<\/p>\nCREATE NONCLUSTERED INDEX IX_Orders_CustomerId\r\nON dbo.Orders(CustomerId);\r\n<\/code><\/pre>\nStep 2: Identify the data<\/h3>\n
OrderDate<\/code>, TotalAmount<\/code>, and Status<\/code>, 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.<\/p>\nCREATE NONCLUSTERED INDEX IX_Orders_CustomerId\r\nON dbo.Orders(CustomerId)\r\nINCLUDE (OrderDate, TotalAmount, Status);\r\n<\/code><\/pre>\nConclusion<\/h2>\n