As a developer who interacts with SQL Server often, the queries I write for the data I need are often slow. Take the query below as an example:

This seems like a very simple query without much room for optimization. In fact, it does not take long to run. By using Query Analyzer in the Microsoft SQL Server Management Studio though, we can see the execution plan reveals a potential bottleneck:

A table scan.

A table scan means that SQL Server will have to look at each row in the table to see if it should be returned for this query. This is slow. It’s very slow. It could not BE slower. In a case like this, we need an index on the table we are querying, and specifically, we need a COVERING index. 

A covering index is an index which contains all of the fields required for a particular query. Now, I won’t go into the pros and cons of creating indexes for every query in your application. That’s something that needs to be evaluated on a case-by-case basis, and quite possibly involves things beyond the normal developer’s control such as disk space. What I will go into is how to make a proper covering index for this query. 

You should be able to use it as a guide for your own tables and queries.

Step 1: Identify All Fields Used in the Query

This includes both the fields in the WHERE clause and the fields in the SELECT.

Step 2: Create Your Index

Order the fields as follows: 

• Fields in WHERE clause first, most distinct values to least distinct
• Fields in SELECT statement

Note: The order of the fields does make a huge difference and should be done deliberately to increase the speed of this particular query.

Step 3 – Test Your Query

Test your query and see what the execution plan says about the Estimated Subtree Cost. The original query was 0.365502. The same query ran with the addition of the covering index is 0.0032831. That’s a reduction of over 99%.

Whenever you introduce a query, it is your responsibility to make sure that query behaves responsibly and does not have a negative impact beyond what is absolutely required. In this case, the query runs quickly enough without the covering index, but considering the table’s growth over time and the sheer number of queries in any application, removing 99% of the impact cannot be a bad thing. 

So, the next time you write a new query or are tasked with making a slow query run faster, consider using a covering index. Slow query? You’ve got that covered!