DBMS Question
What are the difference between Clustered and a Non-clustered index?

Question

DBMS Question

What are the difference between Clustered and a Non-clustered index?

Accepted Answer

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

Clustered index determines the physical order of data in a table
Non-clustered index has a separate structure that includes a copy of the indexed columns and a pointer to the actual data
A table can have only one clustered index, but multiple non-clustered indexes
Clustered index is faster for retrieving large ranges of data, while non-clustered index is faster for retrieving specific data

Accepted Answer

1) With a Clustered index the rows are stored physically on the disk in the same order as the index. Therefore, there can be only one clustered index. A clustered index means you are telling the database to store close values actually close to one another on the disk.

2) With a Non Clustered index there is a second list that has pointers to the physical rows. You can have many non clustered indices, although each new index will increase the time it takes to write new records.

3) It is generally faster to read from a clustered index if you want to get back all the columns. You do not have to go first to the index and then to the table.

4) Writing to a table with a clustered index can be slower, if there is a need to rearrange the data.

Top JPMorgan Chase & Co. Full Stack Developer interview questions & answers

Popular interview questions of Full Stack Developer