Clustered vs. Non-Clustered Index in DBMS

In a database management system (DBMS), a clustered index defines the physical order of the table data based on the indexed column. This means that the rows in the table are stored on disk in the same order as the index, making retrieval of data faster.

On the other hand, a non-clustered index does not dictate the physical order of the table data. Instead, it contains pointers to the actual row locations. This can result in slower data retrieval compared to a clustered index.

While a table can have only one clustered index, it can have multiple non-clustered indexes. Clustered indexes are ideal for columns that are frequently used in range queries, while non-clustered indexes are beneficial for columns that are often used in joins or where clauses.