Do you know if there's any way of doing this in SQL Server (2008)?
I'm working on a DataWarehouse loading process, so what I want to do is to drop the indexes of the partition being loaded so I can perform a quick bulk load, and then I can rebuild again the index at partition level.
I think that in Oracle it's possible to achieve this, but maybe not in SQL Server.
thanks, Victor
I know it wasn't possible in SQL 2005. I haven't heard of anything that would let you do this in 2008, but it could be there (I've read about but have not yet used it). The closest I could get was disabling the index, but if you disable a clustered index you can no longer access the table. Not all that useful, imho.
My solution for our Warehouse ETL project was to create a table listing all the indexes and indexing constraints (PKs, UQs). During ETL, we walk through the table (for the desired set of tables being loaded), drop the indexes/indexing constraints, load the data, then walk through the table again and recreate the indexes/constraints. Kind of ugly and a bit awkward, but once up and running it won't break--and has the added advantage of freshly built indexes (i.e. no fragmentation, and fillfactor can be 100). Adding/modifying/dropping indexes is also awkward, but not all that hard.
You could do it dynamically--read and store the indexes/constraints definitions from the target table, drop them, load data, then dynamically build and run the (re)create scripts from your stored data. But, if something crashes during the run, you are so dead. (That's why I settled on permanent tables.)
I find this to work very well with table partitioning, since you do all the work on "Loading" tables, and the live (dbo, for us) tables are untouched.
No, you can't drop a table's indexes for just a single partition. However, SQL 2008 provides a methodology for bulk-loading that involves setting up a second table with exactly the same schema on a separate partition on the same filegroup, loading it, indexing it in precisely the way, then "switching" your new partition with an existing, empty partition on the production table.
This is a highly simplified description, though. Here's the MSDN article for SQL 2008 on implementing this: http://msdn.microsoft.com/en-us/library/ms191160.aspx