Maintaining indexes on your table is an important part of keeping your database healthy and its performance adequate. There are two maintenance operations for any index: rebuilding and reorganizing. Both operations were designed to get rid of index fragmentation, but differ in how this is done.
|What it does||Drops the existing index and recreates it from scratch.||Physically reorganizes the leaf nodes of the index.|
|When to use||Index fragmentation >= 40%||Index fragmentation >= 10% and < 40%|
|Impact on system||High.|
Database will be offline during process, unless you have Enterprise Edition and have enabled the ONLINE option. Online rebuild requires more resources than offline rebuild.
Reorganize runs on online database.
|Common options||Process can be run on all indexes of all tables on a database, or can be narrowed down to specific databases, specific tables, or even a single index.|
|Function-specific options||Enteprise Edition has an option available to perform an online rebuild of the index. This keeps the index available during rebuild, allowing database access.||N/A|