Here is a quick question,

I know it is quicker to load data with out any indexes, but if there
NEEDS to be indexes on the table is it quicker to load data with the
index there, OR drop the indexes, Load the Data, and then create the
indexes.

Thanks in advance
JPO

In 7 it was certainly true that indices on loading data did not help. In
2000 this is not quite so much the case. Indices remember do need to be
updated on a table when inserting data so unnecessary indices will really
hurt you. Consider though that you may need the indices whilst inserting
data. Perhaps you also elect from the table to determine what rows are
inserted.

Keep them to a minimum is my motto. Try it. Compare the WITH to the
WITHOUT and then an index rebuild.



--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


<jpo (AT) bigpond (DOT) net.au> wrote

If you have a clustered index on the table (which most tables should),
inserting the data in the order of the clustered index is usually a good
thing. If you have tables that get updated and deleted from, you should have
a clustered index on the table as heap tables (tables with no clustered
index) reclaim empty space and can cause you pains upon loading data. I
don't believe having non-clustered indexes on the table you are loading into
will do anything but slow you down, although you would have to weigh your
time restrictions on dropping and recreating them versus just leaving them.

HTH

--
Ray Higdon MCSE, MCDBA, CCNA
---
<jpo (AT) bigpond (DOT) net.au> wrote