Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / SQL-Server / SQL-Server-2008

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

4.00/5 (3 votes)
13 Oct 2009CPOL1 min read 23.2K  
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.

SQL
-- 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

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

Method 2: Creating Included Column Non-clustered Index

SQL
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

History

  • 13th October, 2009: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)