[SQL Server Performance] Speed up your query with covering index

Do you use the same statement often? Are you facing performance issues on that query? Covering index is what you need :) (Ofc depends on your statement/data size)

So what is covering? Covering index is a technique of designing indexes for a table, not a type of index. I will explain this technique with an example.

Lets take Orders table from Northwind sample db. Orders table has a clustered index on CustomerID. We would like to see shipping fee for daily basis. To get this data execute to following query.

select OrderDate, SUM(Freight) from Northwind.dbo.[Orders] where group by OrderDate





As you can see from execution plan, it scans all clustered index data then sorting and grouping the result from index scan.

Lets put an index for OrderDate and Freight fields and see what happens

CREATE INDEX IX_OrderDate_Freight ON dbo.ORDERS(OrderDate, Freight)

then execute the same query

select OrderDate, SUM(Freight) from Northwind.dbo.[Orders] where group by OrderDate







It is directly accessing to the index and getting all the necessary information from index. Normally if sql scans a non-clustered index and there are some fields in the statement which does not exist in the index, Sql Server engine goes to original record to get the field values for every single record. This may cause a performance issue. In this example as it gets all necessary information from index it will speed up the query. Additionally as non-clustered index is sorted, it saves the engine from making additional sorting process.

Note: When you select your index, try to pick small size ones like smallint, integer, datetime. If you have bigger size fields then the implementation may cost more then you had. Try to prevent to put index on the table which has big number of insert/update/delete operations as well. Because everytime a record is changed, indexes will be reviewed from SQL Server Engine to see if they need to be sorted again.