CODEWITHSUNDEEP
sqlsql-servert-sqlindexingquery-optimization
mikelowry
mikelowry

Reputation: 1727

Am I supposed to drop and recreate indexes on tables every time I add data to it?

I'm currently using MSSQL Server, I've created a table with indexes on 4 columns. I plan on appending 1mm rows every month end. Is it customary to drop the indexes, and recreate them every time you add data to the table?

Upvotes: 0

Views: 430

Answers (2)

Philip Kelley
Philip Kelley

Reputation: 40319

I don't think it is customary, but it is not uncommon. Presumably the database would not be used for other tasks during the data load, otherwise, well, you'll have other problems.

It could save time and effort if you just disabled the indexes:

ALTER INDEX IX_MyIndex ON dbo.MyTable DISABLE

More info on this non-trivial topic can be found here. Note especially that disabling the clustered index will block all access to the table (i.e. don't do that). If the data being loaded is ordered in [clustered index] order, that can help some.

A last note, do some testing. 1MM rows doesn't seem like that much; the time you save may get used up by recreating the indexes.

Upvotes: 0

GMB
GMB

Reputation: 222482

Don't recreate the index. Instead, you can use update statistics to compute the statistics for the given index or for the whole table:

UPDATE STATISTICS mytable myindex;   -- statistics for the table index
UPDATE STATISTICS mytable;           -- statistics for the whole table

Upvotes: 1

Related Questions