Deleting rows from a table cause index fragmentation?

Question

I removed some rows from a very large table. Then I ran a query that usually runs within a few seconds and found it to be running very slowly after deleting the rows. I re-built my index and ran my query and found it to be fast again. Could deleting those rows caused the index to be fragmented?

Answer 1

Yes, deleting rows affects the index and maintenance should take place to keep the index relatively in sync with existing data.

Rebuilding an index was likely unnecessary - you only need to do this if the physical fragmentation is 30 percent or more according to MS documentation. REORGANIZE is usually a better choice - think of it as defraging the index.

This is a good article series on SQL Server Index Fragmentation.