Do I Need to Rebuild my non-Clustered indexes

[sfriedman451]

It is my understanding that the non-clustered indexes are not automatically updated with new row inserts. Is there anyway to tell SQL server 7.0 how frequently to rebuild the indexes? Is there any way to know when it was last rebuilt?

 

[bperry]

All relevant indexes are automatically updated when data rows are inserted or updated.

What you may be thinking about are 'index statistics'. SQL Server keeps statistics on the distribution of index values. The default setting of an SQL installation is to automatically update indexes, although it doesn't necessairily update these statistics at every insert. SQL uses some kinda formula to update the stats 'after every so many inserts', when the server gets some idle time.

Index statistics are used by the Query Analyzer to help create an execution plan for queries: wildly out-of-date stats will cause the QA to construct a poor or inefficient plan - which is why 'stats auto-update' is on by default: to keep them reasonably up-to-date.


bperry