SQL2005: Alter.Index

Managing indexes in SQL Server 2005 has changed. All the index management functions are now located in a new and more standardized, ALTER INDEX statement. The DBCC DBREINDEX, DBCC INDEXDEFRAG and DBCC SHOWCONTIG commands are now deprecated and will be removed in future versions.

a) To rebuild an index on a table, we now use the REBUILD keyword like this:

ALTER INDEX PK_CustomerID ON Customers
REBUILD

b) To rebuild all indexes on a table, we can use the ALL keyword like this:

ALTER INDEX ALL ON Customers
REBUILD

c) To handle index fragmentation, use the REORGANIZE keyword like this:

ALTER INDEX IX_CustomerName ON Customers
REORGANIZE

d) To disable an index, use the DISABLE keyword like this:

ALTER INDEX IX_CustomerName ON Customers
DISABLLE

Note: To re-enable the index, use the REBUILD keyword as shown earlier.

e) To view size and fragmentation information, use the sys.dm_db_index_physical_stats catalog view instead. Example:

SELECT * FROM sys.dm_db_index_physical_stats