Reindexing vs Update statistics

[mgole]

Hi

We are upgrading from sql 7 to 2000.During the upgrade process do we have to do a reindexing of all tables or will update statistics take care of that.

Or do we have to do both?
What is the difference between reindexing and update statistics.

Thanks

Madhukar Gole

[skhanal]

Reindexing drops and creates the indexes, so it will take longer compared to update statistics, which only gathers information on indexes (does not change anything on the index).

Reindexing is recommended if your index is fragmented or there were many inserts and deletes on the table and the index b-tree is skewed. That means it is occupying empty spaces on one side of the tree and continues to grow on other side.

Use dbcc showcontig to check fragmentation.

[Rawhide]

If you database is offline or at a low traffic point, run DBCC DBReindex. If your database is online and experiencing moderate traffic, run DBCC INDEXDEFRAG until you can take the database offline or the traffic is low.

I have two high activity tables for which I run DBCC INDEXDEFRAG every 2 hours throughout the day. Then during the slowest time of the night, I run DBCC DBReindex once daily.

[rmiao]

Rebuilding index will update statistics as well.

[mgole]

Thanks for all your replies.
Does the database have to be offline while performing update statistics?

Mdhukar Gole

[Rawhide]

No