See Also: Main_Page - Database Administration - Database Design - Indexing

How Inserts Affect Indexes

When a row is inserted into a table, rows are also added to all non-clustered indexes. If a table has a clustered index, new rows are inserted according to the order of the clustered index. For instance, if the clustered key is last_name, then a new row with a last name of "Brown" will be inserted on the data page containing other last names beginning with the letter "B". If a table does not have a clustered index, then SQL Server checks Page Free Space (PFS) pages to find a data page that has free space available. Therefore, in a heap a row with a last name of "Brown" could be inserted on the same page as the last name of "Smith".

How Deletes Affect Indexes

When a row is removed from a table, corresponding rows are also removed from all non-clustered indexes. The index from which a row was removed will have some free space on the page where the row used to reside. If the deleted rows are not replaced with new rows, the index becomes fragmented.

How Updates Affect Indexes

When a row is updated, changes that have to occur in indexes depend on the columns that get changed. If the clustered index key is updated, then the row might have to be moved from one page to another. For example, if we update the last name of "Brown" to "Paulsen" and the clustered index is on the last_name, then the row will have to be moved to the page containing last names that begin with "P". Non-clustered indexes will be updated if the non-clustered keys are updated or if clustered index's key value is updated. On the other hand, if we update the first_name column, and there is no index containing this column then none of the indexes have to be updated.

So the net effect of data modifications is adding overhead to tables with indexes. If you have appropriate indexes they can actually speed up data modifications. However, if you have numerous indexes on tables with constant data changes your performance may suffer.

More SQLServerPedia Articles on Indexes

How SQL Server Indexes Work

• Index Overview - the basics of how indexes work.
• Data Pages & Extents - about how indexes and data are stored on disk.
• Table and Record Structure - more about the mechanics of data storage.
• Effects of Data Modifications - the more indexes you have, the longer inserts/updates/delete take.
• Index Statistics - the basics of how statistics affect query execution.

Types of Indexes in SQL Server

• Included Columns - starting with SQL Server 2005, you can add fields to indexes with a smaller performance hit.
• Indexed Views - how to build indexes on top of views for more speed.
• XML Indexes Overview - how to index XML documents and fragments.
• XML Index Rules - some additional considerations and requirements for XML indexes.

Best Practices on How to Design Database Indexes

• Indexing Strategies - choosing between clustered/nonclustered, long/short, selectivity, and more.
• Best Practices for Creating Indexes - tips to start with when designing a new index strategy.
• Choosing Index Keys - how to pick which fields to use for a primary key.
• Index Selectivity and Column Order - how to pick which columns to put first, and how it affects different kinds of queries.
• Wide Index Performance - does adding more fields to an index cause it to run slower?
• Tuning Indexes - including articles on the Index Tuning Wizard and Database Tuning Advisor.

Maintaining Indexes for Top Performance

Indexes need regular maintenance in order to perform well.

• Index Maintenance Tutorial - video and script explaining how to defragment and rebuild your indexes automatically.
• Index Maintenance in SQL Server 2005 and Beyond - SQL 2005 introduced new syntax to ALTER INDEX.
• Index Maintenance Overview - analyzing fragmentation and statistics to get the best performance possible out of your indexes.