We have a table that is currently ~4TB in size. We wish to introduce partitioning on this table. The table is already clustered on the key we wish to partition on.
The options appear to be
- Use ALTER TABLE SWITCH to switch the data of the table to a range-partitioned table that has only one partition (zero cost). Then call ALTER PARTITION FUNCTION for each partition we wish to introduce (definitely not zero cost)
- re-create the clustered index using DROP_EXISTING=ON
We're not using SQL Enterprise, so there's no ability to do this online.
Running these queries locks the entire table.
- Even if we accepted the lock, we have no real way of estimating how long this would take (and whether we could complete it in time over a weekend)
- Most of the data is archive data, there's only a small slice of data that's actually 'live' and would be a problem when it's locked. Trying to figure out if there's a strategy using ALTER PARTITION and ALTER TABLE SWITCH that allows us to do most of this with the data swapped out on a staging table.
- Can anyone suggest a way we can do this progressively?