What is the difference between a clustered and a non-clustered index?

A clustered index determines the physical order of data in a table, while a non-clustered index does not affect data order.

A clustered index is a type of index that reorders the way records in the table are physically stored. Therefore, a table can have only one clustered index. The leaf nodes of a clustered index contain the data pages. This means that the clustered index is the table. This is why they are also referred to as an index-organised table. When you query a table with a clustered index, the database can find the data more quickly because it knows exactly where to look. However, because the data is physically reordered, operations that involve a lot of data movement can be slower.

On the other hand, a non-clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non-clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows. Each index row contains the non-clustered key value and one or more row locators that point to the data row having the key value. The data rows may be distributed anywhere throughout the table regardless of the value of the indexed column or columns. You can have multiple non-clustered indexes on a table, which can make querying data faster. However, they also take up more storage space and can slow down the speed of write operations.

In summary, the main difference between a clustered and non-clustered index lies in the way they store and sort data. A clustered index sorts and stores the data rows in the table based on their key values, whereas a non-clustered index maintains a separate record of this data, which allows for more flexibility but at the cost of additional storage and potential write speed reductions.