[dba-SQLServer] Drop restore indexes

jwcolby jwcolby at colbyconsulting.com
Sun Mar 30 19:56:14 CDT 2008


Thanks, the Disable thing sounds like a good option.   


John W. Colby
Colby Consulting
www.ColbyConsulting.com 
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Asger Blond
Sent: Sunday, March 30, 2008 5:28 PM
To: 'Discussion concerning MS SQL Server'
Subject: Re: [dba-SQLServer] Drop restore indexes

John,

Two options:
1. If your indexes are non-clustered the easiest way is: a) ALTER INDEX
indexname ON tablename DISABLE - b) Do your appends - c) ALTER INDEX
indexname ON tablename REBUILD.
2. If your indexes are clustered the first option is a no go, because
disabling a clustered index will make the table inaccessible. Instead you
can use SSMS to generate scripts for create and drop index, as pointed out
by Arthur. In SSMS use Object Explorer to expand your table, expand Indexes,
right-click an index and choose Script Index As -> Create / Drop. Repeat
this for all indexes and all tables wanted. Then a) Run drop indexes scripts
- b) Do your appends - c) Run create indexes scripts.
For both options you can create a sproc for the DISABLE or DROP and another
sproc for the REBUILD or CREATE on all wanted indexes on all wanted tables a
once.

Asger


-----Oprindelig meddelelse-----
Fra: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af jwcolby
Sendt: 30. marts 2008 19:20
Til: 'Discussion concerning MS SQL Server'
Emne: [dba-SQLServer] Drop restore indexes

I am looking for a generic method to drop and then restore the indexes on a
table.  IOW, I have a bunch of tables that I have to do appends to, tens of
millions of records.  These tables may have dozens of indexes.  I have
always set up my indexes using the wizard so I don't have scripts already
set up to create the indexes.  I need to have some tool that will build
scripts to recreate the indexes fond on a table and then remoce the indexes.
Once the records are appended, I need to then run the scripts to rebuild all
the saved index scripts.

Is there such a tool?

John W. Colby
Colby Consulting
www.ColbyConsulting.com 

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

 








































































































































































_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com





More information about the dba-SQLServer mailing list