Click here to Skip to main content
13,086,662 members (58,552 online)
Click here to Skip to main content
Add your own
alternative version


10 bookmarked
Posted 13 Oct 2009

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

, 13 Oct 2009
Rate this:
Please Sign up or sign in to vote.
Query Optimization – Remove Bookmark Lookup – Remove RID Lookup – Remove Key Lookup


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

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

Method 2: Creating Included Column Non-clustered Index

CREATE NONCLUSTERED INDEX [IX_OneIndex_Include] ON [dbo].[OneIndex]

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.



  • 13th October, 2009: Initial post


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


About the Author

India India
Pinal Dave is a Microsoft Technology Evangelist (Database and BI). He has written over 2200 articles on the subject on his blog at Along with 8+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). He is co-author of two SQL Server books - SQL Server Programming, SQL Wait Stats and SQL Server Interview Questions and Answers. Prior to joining Microsoft he was awarded Microsoft MVP award for three continuous years for his contribution in community.

You may also be interested in...


Comments and Discussions

SuggestionMessage Closed Pin
14-Jan-14 1:42
membersreeharikatageri14-Jan-14 1:42 
GeneralYeah, but .. Pin
Pavel Urbancik14-Oct-09 2:07
memberPavel Urbancik14-Oct-09 2:07 
GeneralRe: Yeah, but .. Pin
voloda214-Oct-09 4:13
membervoloda214-Oct-09 4:13 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.170813.1 | Last Updated 13 Oct 2009
Article Copyright 2009 by pinaldave
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid