It is a good idea to drop and re-create the indexes to improve the
performance
--
HTH,
SriSamp
Please reply to the whole group only!

"GriffithsJ" <jonathan.griffiths (AT) ipuk (DOT) com> wrote

Hi SriSamp

Thanks for that. However, is there a way of simply disabling the currently
existing indexes and re-enabling them afterwards? I ask this because the
indexes that may be there when I design the DTS package may not be the same
ones that exist when the DTS package is run (the database schema may
change).

Alternatively, is there a way to identify at run time the indexes that
exist, store all the information necessary to re-create these indexes, drop
these indexes, import the data and them rebuild the indexes from the stored
information?

Any code examples would be much appreciated.

Thanks

Griff

Whilst SriSamp is correct in the main the benefits to be had by removing
indices was reduced with the move from SQL Server 7 to 2000. It may be
beneficial to keep them in if you need to select from the destination whilst
doing a load. Try it and see.

You can script out the indicies with a drop and recreate statement by using
SQLDMO and SQLServerAgent.

--


Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"GriffithsJ" <jonathan.griffiths (AT) ipuk (DOT) com> wrote

If you want to get the list of indexes defined on a table, you can use a
script like the following:
DECLARE @tableName VARCHAR(50)
DECLARE @sqlString VARCHAR(8000)
BEGIN
CREATE TABLE #tableIndexes
(
index_name VARCHAR(200),
index_description VARCHAR(1000),
index_keys VARCHAR(1000)
)

SET @tableName = 'authors'
SET @sqlString = 'INSERT INTO #tableIndexes EXEC sp_helpindex ' +
@tableName
EXEC (@sqlString)

SELECT * FROM #tableIndexes
DROP TABLE #tableIndexes
END
The above script will get all the indexes into the temporary table called
#tableInexes. You can then iterate through this table and form the command
for dropping the index and execute the same. For creating an index back, you
need to play-around the same set of tables or probably "sysindexes". I do
not have any script for this, but someone in the group might post it.
--
HTH,
SriSamp
Please reply to the whole group only!

"GriffithsJ" <jonathan.griffiths (AT) ipuk (DOT) com> wrote

This has been reported as bug. Check out:
http://support.microsoft.com/default...NoWebContent=1
--
HTH,
SriSamp
Please reply to the whole group only!

"GriffithsJ" <jonathan.griffiths (AT) ipuk (DOT) com> wrote