Cluster Index and Non-Clustered Index - Explained

Overview of SQL Server index

In this article I am going to discuss about SQL Server index. At first I will give you a book example, we all are reading books. So, the index page of a book, which contains all the information about book’s chapters and their page numbers are enough information to find out a page or a chapter in a book. Similarly, In SQL Server indexes are the set of pages that arranged in a tree structure (B-Tree), which is hierarchical in nature. Basically two types of indexes are available in SQL Server.

• Clustered index
• Non-clustered index

These two cluster and noncluster indexes and their working procedure I will discuss after describing the basic structure of index in SQL Server.

Basic Structure of Index in SQL Server

The SQL Server creates indexes on columns in a table or views and provides a rapid way to access the data according to the values. Indexes are helpful in enhancing the overall performance of SQL Server.

There are three filtering levels, which helps in faster access of the data.

• Root Level
• Intermediate Level
• Leaf Level

Root Level: This is the highest level of the index. If the table is large and the search is performed against an indexed column, the query engine starts finding from the root node and the root node navigates down to the intermediate nodes and intermediate node navigates down through leaf node the search query is not found. There is only a single root page in an index.

Intermediate Level: This is the middle level of the index. The intermediate level comes in between Root Level and Leaf Level. If the table is too small then there is no intermediate level.

Leaf level: This is the last level of the index. If the search not found between Root Level and Intermediate Level, then the query engine navigates down to Leaf Level.

So, these three indexing level sort the data pages and then helps the query engine to find the searched result quickly and enhance the performance of SQL Server.

Now the question is that, how these indexing levels work on cluster and noncluster index?

What is Cluster and Noncluster Index?

Clustered Index

In a clustered index the data rows are stored at leaf leaf level of the index and the indexed values of a clustered index are sorted in either ascending or descending order. It is possible that, there can be one clustered index available on a view or table. The data is sorted in a table if there is clustered index defined on that table and that table is known as clustered table. A table that doesn’t have any clustered index, is called as heap.

Non-Clustered Index?

The B-tree works same in nonclustered indexes as it works in clustered indexes, but the differences are that the data pages are same, but the leaf pages are different and each row in a table has a one key-pointer that is stored by leaf level. The page pointers and index keys are stored by the leaf-level pages, And also a pointer of the row offset table on the data pages. By merging page pointer and row offset number is also called the row ID. The index keys and page pointers of other index pages are stored by root and intermediate levels. With the same size of keys, The nonclustered indexes need more space than clustered indexes.

Differences between Clustered and Non-Clustered Indexes:

Clustered Index 

• A Table can have ONLY 1 Clustered Index.
• A Clustered Index always has Index Id of 0.
• A Primary Key constraint creates a Clustered Index by default.
• A Primary Key constraint can also be enforced by Nonclustered Index, You can specify the index type while creating Primary Key.
• If the table does not have Clustered Index it is referred to as a "Heap".
• The leaf node of a Clustered Index contains data pages of the table on which it is created.
• Clustered Index enforces a logical order on the rows. Rows are ordered based on Clustering Key.

Nonclustered Index 

• Prior to SQL Server 2008 only 249 Nonclustered Indexes can be created. With SQL Server 2008 and above 999 Nonclustered Indexes can be created.
• Nonclustered Indexes have Index Id > 0.
• A Unique Key constraint created a Nonclustered Index by default.
• A Unique Key constraint can also be enforced by Clustered Index, You can specify the index type while creating Unique Key
• Nonclustered Index does not order actual data, It only orders columns present in the Nonclustered Index based on Index Key specified at the time of creation of Nonclustered Index.
• A table may not have any Nonclustered Indexes.
• The leaf nodes of a Nonclustered Index consists of Index pages which contain Clustering Key or RID to locate Data Row.
• When Clustered Index is not present leaf node points to Physical Location of the row this is referred to as RID. When a Clustered Index is present this points to Clustering Key (Key column on which Clustered Index is created).