sql server programming: Hi,
If the leaf level of Clustered Index is the actual data page, is that
necessary to reorganize the clustered index? I undstand the non-leaf and
root level but should not be at the leaf level. Am I right?

Thanks

I don't know if I understand your question properly but yes you do want to
defrag the leaf level. Why would you not want to? Otherwise how would you
properly set the fill factors and defragment the data itself?

--
Andrew J. Kelly SQL MVP

[quoted text, click to view]

Say you had a column and the clustered index is on that column and that
column alone, now, the values inserted into that column are always inserted
at the end of the table (for want of a better description), for example, the
column has the IDENTITY property.

Also, there are no deletes to the table.

Now, the index can never get fragmented right?

Logically - no; however - the rows may have been inserted fragmented in the
first place because of the availability and location of free extents in the
database. Basically, your table could be mixed up with other tables which
can effect read aheads.

So, to answer your question - its still worth rebuilding indexes but you
base that decision by looking at fragmentation stats out of dbcc showcontig
or sys.dm_db_index_physical_stats.

Tony.

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]


[quoted text, click to view]