SQL SERVER – Query Optimization – Remove Bookmark Lookup – Remove RID Lookup – Remove Key Lookup – Part 3

Earlier I have written two different articles on the subject Remove Bookmark Lookup. This article is as part 3 of original article. Please read first two articles here before continuing reading this article.

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

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

We read in above articles that we can remove bookmark lookup 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 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 following non clustered index. Please note that we do not have to create both the index together. We can create either covering index or included column index along with it. Please note, I am suggesting to create either of it, not both of it.

In 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 clustered index. All non clusteredindex automatically contains pointers to clustered index any way.

We should create index described in earlier article as following.

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-lustered 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 that if both the index usages are forcing index seek instead of index scan.

SQL SERVER - Query Optimization - Remove Bookmark Lookup - Remove RID Lookup - Remove Key Lookup - Part 3 keylook6

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 (https://blog.sqlauthority.com)

Related Post:

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

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

SQL SERVER – Query Optimization – Remove Bookmark Lookup – Remove RID Lookup – Remove Key Lookup – Part 3

, ,
Previous Post
SQLAuthority News – Accessing SQL Server Databases with PHP
Next Post
SQLAuthority News – First Editorial – T-SQL Challenges Beginners

Related Posts

2 Comments. Leave new

  • Hi Pinal,

    Just a small update.

    If your non clustered index is unique, then the clustered indexed columns are NOT included at non leaf levels.

    For non unique non clustered index, what you say holds good.

    Regards,
    S.V.Nagaraj

    Reply
  • Thanks Pinal for great article.

    Reply

Leave a Reply

Menu