• When I have a choice where, based on my constraints, I could reasonably rebuild or reorg, I always rebuild. In general, I've operated with set maintenance windows, where I could easily have large tables locked for hours when they required defragmenting.

    Don't bother with "small" tables, where 7 pages < "small" (8 pages per extent; less than an extent and it doesn't matter), and it is generally taken that "small" < 1001 pages (no particular reason).

    Note that nonclustered indexes often invalidate the "I always add records sequentially and never update any", since they're usually starting with a field that isn't added sequentially.

    Note first that the general (and MSDN) 5-30%, 30%+ thresholds were made up to be reasonable starting points, and the real answer is "it depends" (http://www.sqlskills.com/blogs/paul/post/where-do-the-books-online-index-fragmentation-thresholds-come-from.aspx)

    Rebuilds can be done in parallel (edition permitting); reorgs cannot.

    Rebuilds update statistics with FULLSCAN (http://www.sqlskills.com/blogs/paul/post/Search-Engine-QA-10-Rebuilding-Indexes-and-Updating-Statistics.aspx)

    For the above two, see Paul Randal's article about heavily skewed updates require updating statistics before the rebuild

    http://www.sqlskills.com/blogs/paul/post/index-rebuilds-depend-on-stats-which-are-updated-by-index-rebuilds!.aspx

    If you care about Scan Density (most don't), rebuilds improve that, while reorgs do not.

    Paul Randal wrote specifically about the differences in http://technet.microsoft.com/en-us/magazine/hh395481.aspx