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.

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