Difference between clustered and non-clustered index

Aruljothy Sundaramoorthy

7y1kDatabases & DBA

please explain the concept for clustered and non-clustered index with a pretext example

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Puneet Kankar7y ago
Accepted answer
Hi Arulijothy,
Some basice differences given below, you can also watch the video link below for better understanding.

1.Clustered Index physically sort all rows while Nonclustered Index doesn't.
2.In SQL, one table can only have one Clustered Index but there is no such restriction on NonClustered Index.
3. In many relational databases, Clustered Index is automatically created on the primary key column.
4. Clustered Index can improve the performance of data retrieval while non-clustered index should be created on columns which are used in the join, where, and order by clause. You can further see the SQL Performance Explained by Markus Winand to learn more about how index work in different databases.

https://www.youtube.com/watch?v=NGslt99VOCw
+2
Sonu Gupta7y ago
Hi,

Go to below link. Its explain with Key Differences with Example

https://www.guru99.com/clustered-vs-non-clustered-index.html
+1
Rajanikant Hawaldar7y ago
Hi Aruljothy, Have a look here,
Clustered Index : Only one per table, Faster to read than non clustered as data is physically stored in index order
Non Clustered Index : Can be used many times per table, Quicker for insert and update operations than a clustered index.
Both types of index will improve performance when select data with fields that use the index but will slow down update and insert operations.
Because of the slower insert and update clustered indexes should be set on a field that is normally incremental ie Id or Timestamp.
SQL Server will normally only use an index if its selectivity is above 95%.
For more info : https://stackoverflow.com/questions/91688/what-are-the-differences-between-a-clustered-and-a-non-clustered-index
Best video: https://www.youtube.com/watch?v=NGslt99VOCw&list=PL08903FB7ACA1C2FB&index=36
https://www.youtube.com/watch?v=ITcOiLSfVJQ
Accept answer if it is correct, Thanks
+1