What is the difference between a Clustered Index and NonClustered Index is SQL?

All topics about programming / development in T-SQL or other languages for SQL Server.
Post Reply
Tilly
Posts: 8
Joined: Wed Jun 26, 2019 4:49 pm

Wed Jun 26, 2019 4:51 pm

I was wondering if anybody could tell me the difference between a Clustered Index and NonClustered Index?
DaveP5
Posts: 29
Joined: Sat Jun 22, 2019 7:58 pm
Answers: 1

Wed Jun 26, 2019 4:59 pm

Hi @Tilly



Nonclustered Index
  • Nonclustered Indexes have Index Id that is greater than zero.
  • There is a Unique Key constraint that is created on 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.
  • A Nonclustered Index does not actually order the data, It only orders columns present in the Nonclustered Index based on Index Key specified at the time of creation of Nonclustered Index.
  • It is possible for a table to 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).
  • It is Quicker for insert and update operations than it is on a clustered index.
Clustered Index
  • It is only possible for a table to have ONE Clustered Index.
  • A Clustered Index always has Index Id of ZERO.
  • A Clustered Index is created by default when creating a Primary Key on a table.
  • 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 often 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.
  • Faster to read than non clustered as data is physically stored in index order.

Hope that helps
Tilly
Posts: 8
Joined: Wed Jun 26, 2019 4:49 pm

Wed Jun 26, 2019 5:00 pm

Thank you very much @DaveP5!
Post Reply

Social Media