Generic Script to Drop and Recreate Indexes

Question

Generic Script to Drop and Recreate Indexes

Ranga N

SSChasing Mays

Points: 642
More actions
October 3, 2005 at 11:58 am

#187950

Hi,
I have to do huge bulk inserts and delete of data on a weekly basis. So I am considering dropping all indexes before the bulk insert and rebuild all indexes after the bulk inserts and deletes, this way the indexes are good and statistics are up to date(will do update statistics after rebuilding the indexes)....
So, have anyone of you have a script which will backup the existing indexes and FK keys , drop them and Create them ...

Thanks,
Ranga

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

caffeinated Ten Centuries Points: 1271 More actions · Answer 1

Quickest and easiest way is to create a database maintenance plan, and schedule it to run after your inserts. You'll find it under the Management folder in SQL Enterprise Manager.

Ranga N SSChasing Mays Points: 642 More actions · Answer 2

My requirement is to:

1.Drop all the indexes from the table

2.Load New Data

3.Delete Old Data

4.Recreate the indexes...

So, I am looking for any script that would script all the existing indexes so i can recreate the indexes after data load/deletion.

Greg Hartlaub SSC Eights! Points: 833 More actions · Answer 3

If the columns for the "New Data" are the same as the "Old Data" why are you required to drop an index?

Sounds like a lot of unnecessary work.

Just run maintenance to rebuild the indexes after your update. (Optimizations and Integrity tabs).

If you absolutely must create new indexes because of a business rule, write a Create Index statement with the DROP_EXISTING option. (See books online for syntax)

Run it after your update.

Greg H

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 4

If the data load volume is high and the table is truncated before the load each time, dropping and recreating indexes after the load may be the ticket. However, you should probably benchmark both methods. 'generic' scripts,, well, the EM will generate 'specific' scripts for you. It's more a question of defining/designing the process and it's alternatives, then testing/benchmarking to find the most efficient for your site. If there are multiple indexes you may want to run these scripts in parallel for example.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Ranga N SSChasing Mays Points: 642 More actions · Answer 5

Thanks for your feedback...

The reason I wanted to drop all the nonclustered indexes is to speedup the bulk insert process..

I actually ran sp_helpindex, stored the results in a work table and recreated the indexes after the bulk load.