Alex Rivera | Logout

What do clustered and nonclustered index actually mean?

Asked 2009-08-09T15:59:41.927
1334

I have a limited exposure to databases and have only used a database as an application programmer. I want to know about clustered and nonclustered indexes.

I googled and I found:

A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages. A nonclustered 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 nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

On Stack Overflow, I found What are the differences between a clustered and a nonclustered index?.

What is the explanation for this in plain English?

sql-server performance indexing clustered-index non-clustered-index
Edit
Report

1 Answer

1336

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.

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

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.

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

answered 2009-08-09T16:05:40.293

Your Answer