Clustered Index vs Non-clustered
Is there any difference between having a clustered index on an identity column
versus a non-clustered index?
AFAIK, the clustered index will physically order the data based on the values
in the index. A non-clustered index does not order the table hence the moniker
"heap table" for tables lacking clustered indexes.
BTW, does anyone know when does a clustered index reorders the data?
Is it on every insert, update or when DBCC DBREINDEX is run?
I would guess the latter but you never know.
Since I am on the subject, I would like to ask a few more questions on the
differences between the 2 indexes.
1. What's the general rule of thumb of using a clustered index over a non-clustered
I should mention that I just read a great article at www.sql-server-performance.com
on "heap tables" not releasing space after rebuilding the indexes whereas
clustered tables will release unused space. I tested and found that to
be the case.
2. Has anyone measured the performance difference between the 2 indexes?
I just changed a clustered index to a non-clustered one and joined against
12 million rows on the one column that the index is based on. The queries
took the exact same time to execute.
-- Android Development Center
-- Cloud Development Project Center
-- HTML5 Development Center
-- Windows Mobile Development Center