CODEWITHSUNDEEP
sqlsql-serversql-server-2014
DataDoer
DataDoer

Reputation: 209

SQL Index - Recreate after DELETE

I have a temp table #Data, that I populate inside a stored procedure.

It contains like 15M rows.

Then I create a clustered index, say IX_Data, for a couple of column of the temp table #Data.

Then I delete from #Data which deletes like 1M rows (keeping the total rows 14M now).

My question: at this point, should I drop IX_Data and recreate it?

#Data is being referred further in the rest of the stored procedure just at one place.

Upvotes: 0

Views: 619

Answers (2)

friol
friol

Reputation: 7096

You don't specify which database you are on (maybe it's Oracle?), but your question seems about fragmentation, since data integrity should be mantained in every database even if you delete a million of rows (so there's no need to drop and recreate an index for data integrity).

So, how do you know if your index is fragmented (so you need to recreate, or better, rebuild it)? Every database has his method. Oracle has internal tables from which you can determine if an object is fragmented.

But it depends on your type of database.

Upvotes: 0

zerkms
zerkms

Reputation: 254926

You should not. Indexes are maintained by dbms automatically and always kept in sync.

That's why it's not recommended to create more indexes than you need since it's a performance penalty for every DML query.

Upvotes: 1

Related Questions