When it comes to optimizing query performance in SQL Server, one of the most powerful tools at your disposal is the use of indexes. Indexes allow the database engine to quickly locate and retrieve the data you need, resulting in faster query execution times.

One type of index in SQL Server is the nonclustered index. Unlike a clustered index, which determines the physical order of the data in a table, a nonclustered index is a separate structure that contains a copy of selected columns from the table, along with a pointer to the actual data. This allows for efficient searching and retrieval of specific rows based on the indexed columns.

However, there can be some confusion when it comes to the recommendation made by the missing index engine in SQL Server. In some cases, it may suggest including the primary key or clustered index key as a key field in a nonclustered index. This recommendation may seem counterintuitive, as the primary key or clustered index key is already included in any nonclustered index key by default.

So why would the missing index engine suggest including the clustered index key in a nonclustered index? The answer lies in specific scenarios where the query optimizer can benefit from having the clustered index key as an included column in the nonclustered index.

One such scenario is when the query is ordering the results based on a nonclustered key. By including the clustered index key in the nonclustered index, the query optimizer can avoid the need for a lookup on the clustered index, resulting in improved performance.

Let’s take a look at an example to illustrate this concept:

CREATE TABLE dbo.testclusteredinclude (
    id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    text1 VARCHAR(30) NOT NULL
)

INSERT INTO dbo.testclusteredinclude (text1) VALUES ('test1'), ('test2')

INSERT INTO dbo.testclusteredinclude (text1)
SELECT text1 FROM dbo.testclusteredinclude WHERE text1 = 'test2'

-- Turn on show actual exec plan
SELECT id, text1 FROM dbo.testclusteredinclude WHERE text1 = 'test1'

CREATE NONCLUSTERED INDEX idx_nc_testclusteredinclude_text1 ON dbo.testclusteredinclude (text1)

SELECT id, text1 FROM dbo.testclusteredinclude WHERE text1 = 'test1'

DROP INDEX idx_nc_testclusteredinclude_text1 ON dbo.testclusteredinclude

CREATE NONCLUSTERED INDEX idx_nc_testclusteredinclude_text1 ON dbo.testclusteredinclude (text1) INCLUDE (id)

SELECT id, text1 FROM dbo.testclusteredinclude WHERE text1 = 'test1'

In this example, we create a table called “testclusteredinclude” with two columns: “id” and “text1”. We then insert some sample data into the table and execute a query to retrieve rows where “text1” is equal to ‘test1’.

Initially, we create a nonclustered index on the “text1” column without including the “id” column. When we execute the query, the query optimizer needs to perform a lookup on the clustered index to retrieve the “id” values for the matching rows.

Next, we drop the nonclustered index and recreate it, this time including the “id” column. When we execute the query again, the query optimizer can now retrieve the “id” values directly from the nonclustered index, eliminating the need for a lookup on the clustered index.

By including the clustered index key as an included column in the nonclustered index, we have improved the query performance by avoiding the additional lookup operation.

It’s important to note that SQL Server does not store an extra copy of the clustered key in the nonclustered index just because it is included in the key or as an included column. The clustered key is already a part of any nonclustered index key by default.

In conclusion, while it may seem counterintuitive to include the primary key or clustered index key in a nonclustered index, there are scenarios where it can improve query performance. By understanding the specific requirements of your queries and the behavior of the query optimizer, you can make informed decisions when creating nonclustered indexes in SQL Server.