Click here to Skip to main content
Click here to Skip to main content

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

, 13 Oct 2009 CPOL
Rate this:
Please Sign up or sign in to vote.
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

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)

Share

About the Author

pinaldave
Founder http://blog.SQLAuthority.com
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 http://blog.sqlauthority.com. 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.
Follow on   Twitter

Comments and Discussions

 
SuggestionMessage Automatically Removed Pinmembersreeharikatageri14-Jan-14 2:42 
GeneralYeah, but .. PinmemberPavel Urbancik14-Oct-09 3:07 
GeneralRe: Yeah, but .. Pinmembervoloda214-Oct-09 5:13 

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

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

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