April 20, 2009 at 8:26 am
I will be inserting data into a Db of size nearly 1TB which has many indexes too, I would like to do this through a sgl agent job
1. drop all indexes
2. insert,update data
3. recreate indexes.
is there any script out there which drops all indexes and recreates them again by itself after the inserts are done.
thanks
April 20, 2009 at 9:04 am
Here is a script that will script out all of your indexes for you (you would want to run this prior to dropping them obviously)
http://www.sqlservercentral.com/scripts/Index+Management/31652/
And then here is another script that will drop indexes on a table for you
http://www.sqlservercentral.com/scripts/Index+Management/30208/
On this second script you would have to run something like sp_MSforeachtable in order to get this done for all tables in the database (or you could certainly base it on a defined list) but it should drop all indexes on a table.
Check out the scripts section of this site. It has all kinds of good things to offer.
April 20, 2009 at 9:50 am
the 1st one scripts out all tables including indexes on system tables which i dont need. I just need to drop and recreate user tables.
and how do i drop all indexes at once before inserting.
April 20, 2009 at 9:53 am
As my tables are partitioned, i dont want to drop indexes on all partitions, i just want to do only on those partiotns where i am inserting data, how wud i do that?
April 20, 2009 at 11:47 am
any idea how to drop and re-create indexes only on few partitions of a table.
April 20, 2009 at 11:52 am
I am pretty sure you can't drop the indexes on just specific partitions, as indexes are created on just specific partitions. It's an all or nothing type of thing.
April 20, 2009 at 1:53 pm
You want to look at the new syntax to alter an index:
ALTER INDEX index ON table DISABLE;
You can use sys.indexes, sys.tables and sys.partitions to identify the appropriate indexes to disable and run the above. Either build dynamic SQL and execute it, hard-code each index, or use a cursor.
Note: with the above, you cannot disable the clustered index. You need the clustered index available or the table is not available.
Once the import process is complete, you can enable all indexes using:
ALTER INDEX ALL ON table REBUILD;
All indexes will be rebuilt.
With that said, are you sure you want to do this? This approach assumes that you are inserting a large percentage of the existing data. For example, the table currently has 100,000,000 rows and you are inserting 50,000,000. If however, you have the same number of rows are are only inserting 1,000,000 - it doesn't make sense to drop/disable the indexes and then take the very large hit to rebuild/recreate the indexes.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply