From BOL (this article):
Rebuilding an index drops and re-creates the index. This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages. When ALL is specified, all indexes on the table are dropped and rebuilt in a single transaction.
Rebuilding an index, except in Enterprise Edition with ONLINE = ON, is a blocking operation. Meaning that the index and table are not avialable during the Rebuild. Rebuilding an index also causes the statistics for that index to be updated.
From the same BOL entry:
Reorganizing an index uses minimal system resources. It defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical, left to right, order of the leaf nodes. Reorganizing also compacts the index pages. Compaction is based on the existing fill factor value.
Reorganizing is NOT a blocking operation, but it does consume resources. Statistics are NOT updated with a reorganization.
The typical recommendation is to reorganize indexes with 10-30% fragmentation and rebuild indexes with > 30% fragmentation. There are also rules of thumb about only doing maintenance on indexes with at least a certain number of pages. This will vary depending on who you talk to.
For more information you should read the BOL article linked above and then this one as well.
There are many existing scripts out there that do index maintenance. 2 I would recommend evaluating are:
Michelle Ufford's Index Defrag Script[/url]
Ola Hallengren's Index Optimize script which is included in his SQL Server 2005 and 2008 - Backup, Integrity Check and Index Optimization[/url] solution
As always, don't use a script without testing it and understanding what it is doing.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question