Nonclustered Index on Clustered Index in SQL Server

We know that Indexes have a single root node structurein SQL Server. SQL Server always uses this root node as the starting point for traversing an index. SQL Server always ensures that the key columns are unique for both types of index. Nonclustered indexes have the same B-Tree structure as clustered indexes only with one significant difference. The leaf node of the nonclustered index contains key values not the actual data. These key values map to pointer or clustering keys that locate rows in the data pages.

How does Nonclustered index implement?

The implementation of the nonclustered index depends on whether the data pages of a table are managed as a Heap or as a clustered index.

1. Heap based Nonclustered Index structure in SQL Server - If nonclustered index is built in a heap then SQL Server uses pointers in the leaf node index pages that point a row in the data pages.
2. Nonclustered Index based on Clustered Index Structure- If we have a table with clustered index, SQL Server builds a nonclustered index on the top of the clustered index structure and SQL Server uses clustering keys in the leaf node index page of the nonclustered index to point the cluster index.

How does Nonclustered Index navigate on Clustered Index Structure?

In this section, we are studying on Nonclustered Index Structure on the top of the Clustered Index structure. To understand its functionality, we could take an alphabet example where all the data on the Nonclustered index is built on the top of the clustered index structure as given below:

How does SQL Server navigate a nonclustered index?

To understand this, we are taking the above example and we need to find out the Alpha No ‘1052’ from our data table where nonclustered index is built on the Alpha No data field and the same table has a clustered index defined on the FirstName also. To pull the records from this table, we are using below script as given below:

SELECT AlphaNo, FirstName, LastName FROM dbo.DataTable WHERE AlphaNo =1052

Nonclustered Index Root node Navigation

SQL Server starts at the root node of the nonclustered index and evaluated that 1052 is greater than or equal to the key value actor 1024 and the compression result is to true. Now, SQL server moves to next key value 1052 on the root node and do the same compression evaluation which is to true again. Now, SQL server moves to next key value 1074 on the root node and do the same compression evaluation which is false. In this case, SQL Server uses the previous key value 1052 on the root node and moves to intermediate node.

Nonclustered Index Intermediate node Navigation

After moving to intermediate node, SQL Server evaluated that 1052 is greater than or equal to the key value actor 1045 where the compression result is to true.  Now, SQL server moves to next key value 1052 on the intermediate node and compression evaluation result is true. In this case, SQL Server moves to Leaf node.

Nonclustered Index Leaf node Navigation

SQL Server reads through the leaf node until it finds 1052 and then uses this point value that allocated with the data page clustering key on the clustered index.

Clustered Index Data Page Navigation

SQL Server reads through the data page until it finds the key value is equal to 1052 in clustered index. SQL Server returns this row to the query processor. The pages in the data chain and the rows in them are ordered on the value of the nonclustered index key.

Conclusion

SQL Server stores its indexes in B-Tree format except ColumnStore indexes where each node in such a tree is represented by a single page or node. With a nonclustered index, there is a second list that has pointers to the physical rows. Nonclustered indexes can be built on either a heap or a clustered index. They always contain a row locator back to the base table.

Index structure in SQL Server

Clustered Index structure in SQL Server

How does SQL Server navigate a clustered index?

Nonclustered Index structure in SQL Server

How does Nonclustered index navigate on a Heap?

References: https://technet.microsoft.com/en-us/library/ms177443(v=sql.105).aspx