Index Maintenance

SQL Server automatically maintains indexes on tables. Every INSERT, UPDATE, or DELETE operation forces SQL Server to update the index information to keep it up to date. This maintenance produces some overhead on these operations.

Any time you insert a new row and you had a clustered index, SQL Server must search for the correct page to insert this new row. If the page is full, SQL Server decides the best way to insert this row, depending on the following conditions:

  • If the row has to be inserted at the end of the table and the last page doesn't have any free space, SQL Server must allocate a new page for this new row.

  • If the row has to be inserted into an existing page and there is enough free space in the page to allocate this ...

Get Microsoft® SQL Server™ 2000 Programming by Example now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.