Sample Header Ad - 728x90

Partitioning a 4TB table while staying online (SQL Server Standard)

6 votes
1 answer
2638 views
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? Thanks!
Asked by James Crowley (223 rep)
Nov 9, 2017, 11:44 AM
Last activity: Jan 3, 2025, 11:01 AM