Microsoft® SQL Server 2012 Unleashed

SQL Server Index Maintenance

SQL Server indexes are self-maintaining, which means that any time a data modification (such as an update, a delete, or an insert) takes place on a table, the index B-tree is automatically updated to reflect the correct data values and current rows. Generally, you do not have to do any maintenance of the indexes, but indexes and tables can become fragmented over time. There are two types of fragmentation: external fragmentation and internal fragmentation.

External fragmentation occurs when the logical order of pages does not match the physical order or the extents allocated to the table are not contiguous. These situations occur typically with clustered tables as a result of page splits and pages being allocated ...