Creating Covering Index to Eliminate Join – SQL in Sixty Seconds #090

Last week, I had published Remove Bookmark Lookup – SQL in Sixty Seconds #089 and I got a tremendous response to the video. Lots of people reached out to me with lots of questions. I have written a follow-up series to this video as well. You may read it here SQL SERVER – 7 Follow Up Answers to Remove Bookmark Lookup. Every day I am getting lots of email about the covering index and how to improve performance so today we are going to see a video about creating converting index to eliminate join and improve query performance.

Covering Index to Eliminate Join

The demonstration which we are going to see today was discussed in detail with my one of the largest e-commerce client during our conversation at Comprehensive Database Performance Health Check. The issue was very interesting and I believe quite many of you might have seen at your organizations as well.

My client had prepared a table which contained lots of reporting data. Whenever they needed data they were just retrieving the data from that table. This was pretty fast most of the time as it was a single table and there was no join etc. However, often when they modify their query and include a few additional columns it was running slow. A quick investigation demonstrated that even though it was a single table query, the execution plan was using join. The query was also using multiple indexes to retrieve data from a single table.

We were able to quickly fix the performance issue by creating a covering index on it. Let us see how we can do it in this quick video.

In this video, I have used sample database WideWorldImporters which you can download from here. If you want to try out this demonstration yourself here is the query which I had used in the demonstration as well.

SELECT [InvoiceID]     
      ,[ContactPersonID]
      ,[AccountsPersonID]
  FROM [WideWorldImporters].[Sales].[Invoices]
  WHERE [ContactPersonID] >= 3032 AND
		[ContactPersonID] <= 3035;

Here is the index which I had used to fix the query.

CREATE NONCLUSTERED INDEX [FK_Sales_Invoices_ContactPersonID] ON [Sales].[Invoices]
(
	[ContactPersonID] ASC
)
INCLUDE([AccountsPersonID]) 

Please remember that is never a good idea to create too many indexes on a single table as well add too many columns on a single index. When you reach this point, I strongly suggest that you explore other methods to tune your query. If you want to discuss this one, you can reach out to me here.

Reference: Pinal Dave (https://blog.sqlauthority.com)