CODEWITHSUNDEEP
sqlsql-server
JollyRoger
JollyRoger

Reputation: 857

Does deleting rows from a table distrupts indexes?

I need to know that if we delete some rows (I am talking for sql server) from a table which has some indexes (clustered or non-clustered, for both situation) can give any damage to indexes or not? What happens to indexes when we delete rows? Which one is better for performance, deleting rows from a table after processing them, or mark up them as processed (When we will need to reuse them like 20 times more). Thanks for the answers.

Upvotes: 2

Views: 8507

Answers (2)

user9192401
user9192401

Reputation: 164

My answer is YES. Index is created on data in the tables and in short if data is deleted from the tables then the levels of fragmentation rise. Rise in fragmentation levels effects the data retrieval in many ways.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271231

I don't know what you mean by "damage". When you delete rows from the table, the index entries need to be deleted as well. This does not "damage" the index per se. At least, the index continues to be useful.

If you have lots of deletes, updates, and inserts, then over time the index will be fragmented. This does affect performance. At some point it becomes useful to re-build the index for performance purposes. You can read about this in the documentation.

I would not worry about rebuilding the indexes because of a handful of deletes. It takes a bit of work to really fragment an index.

Upvotes: 5

Related Questions