Differences between Clustered Index and NonClustered Index

The differences between Clustered Index and NonClustered Index is one of the most common interview question. Both Clustered Index and Nonclustered Indexes have same physical structure in SQL Server. Both are stored as a B-Tree structure in SQL Server.

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.
Faster to read than non clustered as data is physically stored in index order.

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).
Quicker for insert and update operations than a clustered index.

Ref: https://www.c-sharpcorner.com/blogs/differences-between-clustered-index-and-nonclustered-index1

Clustered Index	Non- Clustered Index
The Clustered Indexes are indexes which will sort the data physically	Non- Clustered indexes does not sort the data physically. These are logical indexes.
Only one clustered index created for one column	User can create up to 999 non- clustered indexes
These indexes are faster to read than non- clustered indexes	Non- clustered indexes are slower in read operation as compare to clustered indexes.
The select operations are fast in clustered indexes	Insert and update operations are fast in non-clustered indexes
Clustered indexes will only sort the table in specific order so it will not consume the physical space	Non-clustered indexes are physically stored indexes which works in logical way.
Clustered index contains data at the leaf node	Non- clustered indexes do not contain data at leaf node.

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.
Faster to read than non clustered as data is physically stored in index order.

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).
Quicker for insert and update operations than a clustered index.

Ref: https://www.c-sharpcorner.com/blogs/differences-between-clustered-index-and-nonclustered-index1

Clustered Index	Non- Clustered Index
The Clustered Indexes are indexes which will sort the data physically	Non- Clustered indexes does not sort the data physically. These are logical indexes.
Only one clustered index created for one column	User can create up to 999 non- clustered indexes
These indexes are faster to read than non- clustered indexes	Non- clustered indexes are slower in read operation as compare to clustered indexes.
The select operations are fast in clustered indexes	Insert and update operations are fast in non-clustered indexes
Clustered indexes will only sort the table in specific order so it will not consume the physical space	Non-clustered indexes are physically stored indexes which works in logical way.
Clustered index contains data at the leaf node	Non- clustered indexes do not contain data at leaf node.

SQL Talk

Differences between Clustered Index and NonClustered Index