SQL Server Solutions - Performance Tips: Consider A Covering Index For Frequent Queries (SQL Server 7/2000)


If you have a particular query that you run more often than just about any other, and the columns in its SELECT and WHERE clauses don't tend to change from execution to execution, consider creating a covering index on those columns. A covering index is one that contains all the required columns in a single index. The benefit here is that all the necessary columns can be returned by the index alone without requiring another lookup in the base table. And because it's an index, these values are already in the correct order for the most effective searching and sorting possible.


Use the CREATE INDEX statement to do this. For example, in the Northwind database, you might use something like this:


  • CREATE NONCLUSTERED INDEX ix_CustomersCovering ON dbo.Customers
  • (
  • CustomerID,
  • CompanyName,
  • ContactName,
  • ContactTitle,
  • Country
  • ) ON [PRIMARY]
  • GO

You'll want to test your performance to make sure you're getting the results you want. Try to limit the number of columns you cover to the smallest reasonable set your applications actually need. The wider an index gets, the more expensive INSERT and UPDATE statements can become.

Go back