Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Bill_C
Constraint Violating Yak Guru
299 Posts |
 Posted - 2010-09-22 : 02:26:09
|
| A couple of questions on indexes:-1. Would my imports work faster if I dropped the indexes on all the tables I am Truncating then repopulating? I have read somewhere (can't remember where though) that this is the case when doing Bulk Inserts, and I read I would also need to reset the statistics as well.2. Is there a way (DMV?) I can get info on all the indexes on all my tables to help facilitate the above? (I would like to know if I can generate a drop and create statement for all the indexes)Thanks. |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-22 : 02:40:28
|
| Bulk Import commands can take a hint if the import data is presorted on clustered index. That will help the import speed.It can help import if non-clustered indexes are dropped before the import and recreated afterwards. You won't need to update stats - the Create Index will do that.However, if you are importing into an existing table that already has a lot of data (relative to the amount you are importing) recreating the index will have to recreate all the pre-existing index rows too, as well as the new ones. If you are importing into an empty table then dropping and recreating will probably be faster (you say you are truncating the table, so that's presumably the case)SSMS can generate a script of all the indexes on a given table for you. |
 |
|
|
Bill_C
Constraint Violating Yak Guru
299 Posts |
Posted - 2010-09-22 : 04:37:49
|
Thanks, some of our imports are for a daterange, but these can still be many millions of rows of data. Others (smaller tables) go for a full dataset which again can be as many as 2 - 5 million rows.quote:
SSMS can generate a script of all the indexes on a given table for you.
Any pointers on how to do this? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-22 : 06:06:41
|
| Open SSMS, right click database, select Tasks : Generate script |
|
|
|
|
|
|
Getting info on existing indexes