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

This article is follow up of my previous article SQL SERVER – Query Optimization – Remove Bookmark Lookup – Remove RID Lookup – Remove Key Lookup. Please do read my previous article before continuing further.

I have described there two different methods to reduce query execution cost. Let us compare the performance of the SELECT statement of the previous query.

We have created two different indexes on the table.

Method 1: Creating covering non-clustered index.

In this method, we will create a non-clustered index that contains the columns used in the SELECT statement along with the column used in the WHERE clause.

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

Method 2: Creating included column non-lustered index.

In this method, we will create a nonclustered index that includes the columns used in the SELECT statement along with the column used in the WHERE clause. Here, we will use new syntax introduced in SQL Server 2005. An index with included nonkey columns can significantly improve query performance when all the columns in the query are included in the index.

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

Let us compare the performance of our query by running the following query with two different indexes.

SELECT ID, FirstName
FROM OneIndex WITH (INDEX(IX_OneIndex_Cover))
WHERE City = 'Las Vegas'
GO
SELECT ID, FirstName
FROM OneIndex WITH (INDEX(IX_OneIndex_Include))
WHERE City = 'Las Vegas'
GO

Let us examine the execution plan and compare the query costs.

In fact, in this example, the performance of both the queries is quite same. In this case, we can use either of the query and obtain the same performance.

I have mentioned in my previous article that I prefer the Method 2. The reason is that method 2 has many advantages over method 1.

1) Index can exceed the 900-byte limitation of the index key.

2) Index can include datatypes that are not allowed as key columns – varchar(max), nvarchar(max) or XML.

3) Size of the key index can be reduced, which improves the overall performance of the index operation.

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