tags:

views:

77

answers:

0
Q: 

SQL Server 2008: Disable index on one particular table partition

I am working with a big table (~100.000.000 rows) in SQL Server 2008. Frequently, I need to add and remove batches of ~30.000.000 rows to and from this table. Currently, before loading a large batch into the table, I disable indexes, I insert the data, then I rebuild the index. I have measured this to be the fastest approach.

Since recently, I am considering implementing table partitioning on this table to increase speed. I will partition the table according to my batches.

My question, will it be possible to disable the index of one particular partition, and load the data into that one before enabling it again? In that case, the rest of my table will not have to suffer a complete index rebuild, and my loading can be even faster?

related questions

How do you know what a good index is?
What datatype should be used for storing phone numbers in SQL Server 2005?
What is the best way to build an index to get the fastest read response?
How would I go about creating a custom search index much like Lucene?
What is a Covered Index?
When should you use full-text indexing?
SQL/Oracle: when indexes on multiple columns can be used
Best use of indices on temporary tables in T-SQL
On Disk Substring index
How to implement a "related" degree measure algorithm?
Non-Clustered Index on a Clustered Index column improves performance?
HTML Help keyword location
One or Two Primary Keys in Many-to-Many Table?
Strategies for keeping a Lucene Index up to date with domain model changes
Do indexes work with "IN" clause
Table Scan vs. Add Index - which is quicker?
Why does SQL Server work faster when you index a table after filling it?
PostgreSQL: GIN or GiST indexes?
What's the difference between a Table Scan and a Clustered Index Scan?
Database Case Insensitive Index?
Testing for inequality in T-SQL
Tables with no Primary Key
How do I index a database field
How does database indexing work?
Why doesn't SQL Full Text Indexing return results for words containing #?