What is clustered and non clustered index?

Question

Accepted Answer

Clustered index determines the physical order of data in a table, while non-clustered index is a separate structure.

Clustered index sorts and stores the data rows in the table based on their key values.
Non-clustered index creates a separate structure that contains the key values and a pointer to the data row.
A table can have only one clustered index, but multiple non-clustered indexes.
Clustered index is faster for retrieving large amounts of data, while non-clustered index is faster for retrieving small amounts of data.
Example: Clustered index on a customer table based on customer ID, non-clustered index on the same table based on customer name.

Accepted Answer

a clustered index, the leaf level pages are the actual data pages of the table. When a clustered index is created on a table, the data pages are arranged accordingly based on the clustered index key. There can only be one Clustered index on a table.

Non-Clustered index, the leaf level pages does not contain data pages instead it contains pointers to the data pages. There can multiple non-clustered indexes on a single table.

What is clustered and non clustered index?

Top TCS SQL Database Administrator interview questions & answers

Popular interview questions of SQL Database Administrator

Top HR questions asked in TCS SQL Database Administrator