Query Optimization – Remove Bookmark Lookup – Remove RID Lookup – Remove Key Lookup

Introduction

Earlier I wrote two different articles on the subject Remove Bookmark Lookup. This article is Part 3 of the original article. Please read the first two articles listed below before continuing to read this article.

1. SQL SERVER – Query Optimization – Remove Bookmark Lookup – Remove RID Lookup – Remove Key Lookup
2. SQL SERVER – Query Optimization – Remove Bookmark Lookup – Remove RID Lookup – Remove Key Lookup – Part 2

We read in the above articles that we can remove bookmark lookups using covering index. Covering Index is the index which contains all the columns used in SELECT as well in JOINs and WHERE conditions. In our example, we have created a clustered index first.

-- Create Clustered Index
CREATE CLUSTERED INDEX [IX_OneIndex_ID] ON [dbo].[OneIndex]
(
[ID] ASC
) ON [PRIMARY]
GO

Based on clustered index, we have created the following non clustered index. Please note that we do not have to create both the indexes together. We can create either covering index or included column index along with it. Please note that I am suggesting to create either of them, not both.

In the earlier article, I have suggested to include all columns but in reality in any non clustered index there is no need to include columns included in the clustered index. All non clustered indexes automatically contain pointers to clustered index any way.

We should create an index described in the earlier article as follows:

Method 1: Creating Covering Non-clustered Index

CREATE NONCLUSTERED INDEX [IX_OneIndex_Cover] ON [dbo].[OneIndex]
(
City, FirstName
) ON [PRIMARY]
GO

Method 2: Creating Included Column Non-clustered Index

CREATE NONCLUSTERED INDEX [IX_OneIndex_Include] ON [dbo].[OneIndex]
(
City
) INCLUDE (FirstName) ON [PRIMARY]
GO

Let us examine the execution plan and compare the query costs and also verify if both the index usages are forcing index seek instead of index scan.

As discussed in the example, any non clustered index does not need to include columns which are included in the clustered index.

Reference

• Pinal Dave (http://blog.sqlauthority.com)

History

• 13^th October, 2009: Initial post