Clustered vs Nonclustered Index

Question

What is the difference between clustered and non clustered database index

Answer

A database index is a special data structure (tree, hash, etc) that improves the performance of data retrieval. There is nothing free so the performance gain comes with slower writes and increased storage space. Database index architecture can be classified into clustered and non clustered.

In a clustered index the ordering of physical data rows is in accordance with index blocks that point to them which means only one clustered index can be created on a given database table. In other words leaf nodes (recall an index is probably a tree data structure) of a clustered index contain the data pages. Clustered indexes increase data retrieval speed only when data is accessed sequentially.

On the other hand in a non clustered index data is present in random order while the logical order is specified by the index which means leaf level nodes contain pointers to the data page and row number. None clustered index is typically created on columns used in JOIN, WHERE, and ORDER BY clauses. It is good for tables whose values may be modified frequently.