The ALTER INDEX statement in SQL Server (and other database systems) allows you to rebuild or reorganize an existing index. Both operations help improve the performance of the database by optimizing the index structure. Here's how to perform each action:
1. Rebuilding an Index
Rebuilding an index creates a new copy of the index and removes the old one. This is typically more resource-intensive but can be beneficial for heavily fragmented indexes.
Syntax
ALTER INDEX <IndexName> ON <TableName>
REBUILD [ WITH ( <option> [, ...] ) ];
Options
- WITH (FILLFACTOR = number): Sets the fill factor for the index pages.
- ONLINE = { ON | OFF }: Allows the index to be rebuilt without locking out access to the underlying table.
Example
ALTER INDEX ix_MyIndex ON MyTable
REBUILD WITH (FILLFACTOR = 80, ONLINE = ON);
2. Reorganizing an Index
Reorganizing an index is a lighter-weight operation that defragments the index pages without creating a new copy. This operation is less resource-intensive and can be performed while the table is online.
Syntax
ALTER INDEX <IndexName> ON <TableName>
REORGANIZE;
Example
ALTER INDEX ix_MyIndex ON MyTable
REORGANIZE;
Notes
- It's generally recommended to check the fragmentation level of indexes before deciding whether to rebuild or reorganize. Common thresholds are:
- 5% to 30% fragmentation: Reorganize
- Above 30% fragmentation: Rebuild
- You can also rebuild or reorganize all indexes on a table by using the following command:
Rebuild All Indexes
ALTER INDEX ALL ON MyTable
REBUILD;
Reorganize All Indexes
ALTER INDEX ALL ON MyTable
REORGANIZE;
Summary
- Use ALTER INDEX ... REBUILD when you need a significant performance enhancement and are okay with a more intensive operation.
- Use ALTER INDEX ... REORGANIZE for a quick, less intensive option that can be done online with minimal impact.