When it comes to optimizing queries in SQL Server, one important concept to understand is the use of indexes. In a previous series of articles, we discussed how to remove bookmark lookup, RID lookup, and key lookup. In this article, we will continue our exploration of this topic.

In the previous articles, we learned that we can remove bookmark lookup by using a covering index. A covering index is an index that includes all the columns used in the SELECT, JOIN, and WHERE clauses of a query. In our example, we created a clustered index first:

CREATE CLUSTERED INDEX [IX_OneIndex_ID] ON [dbo].[OneIndex]
(
[ID] ASC
) ON [PRIMARY]
GO

Based on the clustered index, we then created a non-clustered index. It’s important to note that we don’t have to create both types of indexes together. We can choose to create either a covering index or an included column index. In a non-clustered index, there is no need to include columns that are already included in the clustered index, as the non-clustered index automatically contains pointers to the clustered index.

Here are two methods for creating the desired index:

Method 1: Creating a covering non-clustered index:

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

Method 2: Creating an included column non-clustered index:

CREATE NONCLUSTERED INDEX [IX_OneIndex_Include] ON [dbo].[OneIndex]
(
City
) INCLUDE (FirstName) ON [PRIMARY]
GO

After creating the indexes, it’s important to examine the execution plan and compare the query costs. We also need to verify if both index usages are forcing index seek instead of index scan. This will help us determine the effectiveness of our index creation.

By understanding the concept of covering indexes and included column indexes, we can greatly improve the performance of our SQL Server queries. Removing bookmark lookup can lead to faster query execution and better overall database performance.

For more information on query optimization and removing bookmark lookup, be sure to check out the previous articles in this series:

• 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

Stay tuned for more articles on SQL Server optimization techniques and best practices!