The index internals has always been an interesting topic for SQL Developers. In this article, we will clarify some of the interesting internal facts about non-clustered and clustered indexes.

Let’s start by understanding the concept of non-clustered and clustered indexes. In a unique non-clustered index, the clustered index key is added to the leaf level of the non-clustered B-Tree structure. On the other hand, in a non-unique non-clustered index, the clustered index key is added to both the leaf and non-leaf levels of the non-clustered B-Tree structure.

To better understand these concepts, let’s create an SQL table and visualize the index structures. Consider a table, dbo.Class:

CREATE TABLE dbo.Class(
  RollNo INT,
  Name VARCHAR(50),
  Age INT,
  MobileNo VARCHAR(10)
);

Now, let’s create a clustered index on this table with the column RollNo as the clustered index key:

CREATE UNIQUE CLUSTERED INDEX cix_class ON dbo.Class (RollNo);

The clustered index sorts and stores the data in a B-Tree structure with index keys at the root and intermediate levels. The table contents are stored in the leaf level of the B-Tree.

Next, let’s create a unique non-clustered index on the table dbo.Class with MobileNo as the index key:

CREATE UNIQUE NONCLUSTERED INDEX nix_demo_mobile ON dbo.Class(MobileNo);

A unique non-clustered index is created with the non-clustered index key being added to the root, intermediate, and leaf level pages. The leaf level page of the non-clustered index also includes the clustered index key, RollNo.

Now, let’s examine some facts about the unique non-clustered index:

• The clustered index key is not included in the root level of the non-clustered index.
• A query with a predicate on the MobileNo column can easily seek through the non-clustered B-Tree structure to reach the exact leaf level page.
• The data of other columns can be fetched using a key lookup operation.

On the other hand, a non-unique non-clustered index includes the clustered index key in both the leaf and non-leaf level pages. This helps in maintaining uniqueness and reducing the number of IO operations.

In conclusion, understanding the internals of non-clustered indexes can help you make better decisions when it comes to SQL indexing. It is important to consider the structure and characteristics of indexes to optimize query performance.

Reference: https://www.youtube.com/watch?v=y_bl9dArtmA