Hi,

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
index?

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.


Michael Tzoanos