Welcome back to another article on SQL Server query optimization. In our previous article, we discussed different methods to reduce query execution cost. Today, we will compare the performance of two different indexes and explore their advantages.

Method 1: Creating a Covering Non-Clustered Index

In this method, we create a non-clustered index that contains the columns used in the SELECT statement along with the column used in the WHERE clause. Let’s take a look at the code:

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

Method 2: Creating an Included Column Non-Clustered Index

In this method, we create a non-clustered index that includes the columns used in the SELECT statement along with the column used in the WHERE clause. This method utilizes the new syntax introduced in SQL Server 2005. Here’s the code:

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

Comparing Query Performance

Now, let’s compare the performance of our query by running it with the 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

After examining the execution plan and comparing the query costs, we find that the performance of both queries is quite similar. In this case, we can use either query and achieve the same performance.

However, I mentioned in my previous article that I prefer Method 2. Here are a few reasons why:

1. The index can exceed the 900-byte limitation of the index key.
2. The index can include datatypes that are not allowed as key columns, such as varchar(max), nvarchar(max), or XML.
3. The size of the key index can be reduced, improving the overall performance of the index operation.

By utilizing Method 2, we can take advantage of these benefits and optimize our query performance.

Thank you for reading this article on improving query performance in SQL Server. Stay tuned for more tips and tricks!