Today, I want to share an interesting observation I made while working on query optimization in SQL Server. This observation relates to the behavior of nonclustered indexes when a clustered index is created on a table.

Let’s start by running an example. First, we need to create a sample table:

CREATE TABLE [dbo].[MyTable](
[ID] [int] NOT NULL,
[First] [nchar](10) NULL,
[Second] [nchar](10) NULL
) ON [PRIMARY]

Next, we’ll insert some data into the table:

INSERT INTO [AdventureWorks].[dbo].[MyTable]
([ID],[First],[Second])
SELECT 1,'First1','Second1'
UNION ALL
SELECT 2,'First2','Second2'
UNION ALL
SELECT 3,'First3','Second3'
UNION ALL
SELECT 4,'First4','Second4'
UNION ALL
SELECT 5,'First5','Second5'

Now, let’s create a nonclustered index on the table:

CREATE NONCLUSTERED INDEX [IX_MyTable_NonClustered]
ON [dbo].[MyTable]
(
[First] ASC,
[Second] ASC
) ON [PRIMARY]

We can now run two queries and observe the execution plans:

SELECT ID
FROM [MyTable]
WHERE First = 'First1' AND Second = 'Second1'

SELECT Second
FROM [MyTable]
WHERE First = 'First1' AND Second = 'Second1'

Surprisingly, the first query uses a table scan, while the second query uses an index seek. This behavior seems counterintuitive since both queries have the same WHERE clause and should ideally use the nonclustered index.

To investigate further, let’s create a clustered index on the table:

CREATE CLUSTERED INDEX [IX_MyTable_Clustered]
ON [dbo].[MyTable]
(
[ID] ASC
) ON [PRIMARY]

Now, if we run the same two queries again, we’ll notice that both queries use a nonclustered index scan. This is unexpected because the first query is retrieving a column that is not included in the nonclustered index.

So, why does this happen?

The reason for this behavior lies in the internal structure of nonclustered indexes. Every nonclustered index in SQL Server refers to the clustered index internally. When a clustered index is created on a table, it reorganizes the table in the physical order of the clustered index. As a result, all nonclustered indexes are also reorganized and they start pointing to the clustered index.

In our case, the column retrieved in the first query is part of the clustered index, and the columns used in the WHERE clause are included in the nonclustered index. This leads to an index seek operation on the nonclustered index.

I hope this article sheds some light on this interesting behavior of SQL Server indexing. If you have any thoughts or suggestions, please feel free to share them with me. I’m open to including your insights in this article.

Thank you for reading!