Fragmented Table with a Non-Clustered Index

[LouBurgoyne]

I have an application that has about 300 tables. approximately 200 of them have no clustered index. The application is using MS SQL 7.0, SP1.

Is there a performance benefit to not using a clustered index?

This became apparent as I was running DBCC Showcontig on my tables. Many are highly fragmented, but do not have a clustered index, so DBCC DBREINDEX ('tablename') does not accomplish anything.

I have found that if I create a clustered index on the table, the indexes are rebuilt and the fragmentation decreases dramatically. I then drop the recently created index to return the table to its original state.

Any feedback would be appreciated.


Lou Burgoyne
Applications Administrator
lburgoyne@ikon.com

 

[tlbroadbent]

You can run REINDEX on individual indexes.

DBCC DBREINDEX ('pubs.dbo.authors', UPKCL_auidind, 80)

The parameters are tbale name, index name and fill factor.

There are definite advantages to clustered indexes most of the time. However, that does not mean a clustered index is always appropriate. Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com