July 15, 2009 at 1:15 am
I have a table with some non clustered indexes on it. Every day I truncate the table and fill it again with insert statements.
How does this affect my indexes?
Is it better to first drop the indexes, then truncate the table and recreate the indexes at the end of the inserts?
Any advice is welcome. Thanks you.
July 15, 2009 at 2:55 am
Is it better to first drop the indexes, then truncate the table and recreate the indexes at the end of the inserts?
Absolutely... performance of the inserts should be much quicker doing it this way.
July 16, 2009 at 8:37 am
Ian Scarlett (7/15/2009)
Is it better to first drop the indexes, then truncate the table and recreate the indexes at the end of the inserts?
Absolutely... performance of the inserts should be much quicker doing it this way.
You also get contiguous and fully-filled index pages this way too, so it is a double win.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 16, 2009 at 10:53 am
TheSQLGuru (7/16/2009)
Ian Scarlett (7/15/2009)
Is it better to first drop the indexes, then truncate the table and recreate the indexes at the end of the inserts?
Absolutely... performance of the inserts should be much quicker doing it this way.
You also get contiguous and fully-filled index pages this way too, so it is a double win.
Do you also have a clustered index on this table? If you don't, consider creating one on a column (or combination of columns) that are unique and you select against alot.
You may want to consider dropping all indexes (clustered and nonclustered), insert, then rebuild.
Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply