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