What is Non-cluster Index in SQL Server?

Before going to start, please go to the how Cluster index works? See in this article Link. Non cluster index works based on cluster index. The main difference is Non cluster index will not store the actual data in the leaf node. As you know Cluster index will store the actual data in the leaf node. The leaf node of Non cluster index pointing to the Cluster index data page with the help of the Cluster index Key’s pointer.

Below are key points that need to remember for Noncluster Index.

• The NonCluster index does not hold the actual data.
• Cluster index key will be added to the leaf node.

Non-Cluster Architecture

Please go through Cluster Index Article, for example, the Student detail table I have explained how Cluster Index and how we can save execution time.

For NonCluster, Here I am taking the same student table with an additional column like Mobilenumber and with more data.

Create Student Table by using the below Script

CREATE TABLE [dbo].[StudentDetailsNonClusterIndex](
[Std_id] [int] NOT NULL,
[Std_Name] [varchar](50) NULL,
[Age] [bigint] NULL,
[MobileNumber] [bigint] NULL,
 CONSTRAINT [PK_studentDetails] PRIMARY KEY CLUSTERED
(
[Std_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Use the below Script to in insert the record dynamically

DECLARE @Int bigint = 10001
While (@Int < 50000 )
BEGIN
INSERT INTO StudentDetailsNonClusterIndexValues ( @Int, 'StudentName' + CAST( @Int as varchar), @Int, @Int + @Int )
SET @Int = @Int  + 1
END

Now we have 49999 data in our table. how we can implement the NonCluster index to save the query execution’s.

Let we will select one record based on Mobile number, now we can see SQL Optimizers will do Cluster Index Scan instead of Cluster Index Seek., its high performance impact here.

Because MobileNumber don’t have any index key, SQL Optimizer will search all record. Let we will create NonClusterIndex against MobileNumber

We can executed the same query and see the execution plan.

What is Key Lookup? The key lookup operator will used by SQL optimizer when an index seek against a specific target table and that index does not have all of the columns needed to fulfill the result set. 

Here if we see are selecting Two Column Std_Name, MobileNumber, But we have noncluster index for only one column “Mobilenumber”, due to this reason SQL Optimizer looking the data from Cluster data page by using Cluster index key.

Note : We will discuss topic separately, how we can avoid the key lookup.

So now based on our above example, the moibleNumber captured from NonCluster index page based on ClusterIndex key and Std_Name will be captured by using Key Loopup against a specific target table.

In upcoming Article we can see how NonCluster Index works internally in SQL Engine.

Thank You…!!

What is Cluster Index In SQL Server ?