Clustered vs Non-Clustered Index in Database - Interview Question Answer

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

When a clustered index is created on a table, the rows in the table are stored on the disk in the same order as the index. This means that the actual data rows are organized at the leaf level of the index based on the clustering key.

On the other hand, a non-clustered index stores the index key values separately from the actual row data. It contains pointers to the location of the actual data rows.

The key difference between the two types of indexes is that a table can have only one clustered index, which defines the physical order of the table, while multiple non-clustered indexes can be created on a table without affecting its physical structure.

Understanding the distinction between clustered and non-clustered indexes is crucial for optimizing database performance and query efficiency in various scenarios.