The Easiest Way to Rebuild a Table in Batch

Mark Wojciechowicz • June 17, 2017

Recently, I had the task of rebuilding a very large table in order to partition it and put it on a columnstore index. While testing this out in the lower environments, I quickly blew up the log and ran out of disk on the server, so I decided to batch it out instead. Below is an example of the query I used.

 --create example source and target tables 
SELECT TOP (101)ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS id 
INTO #source
FROM sys.columns;

CREATE TABLE #target (id int); 

--while data exists in the source, delete from source and insert into target 
WHILE EXISTS (SELECT 1 FROM #source)
  BEGIN
    BEGIN TRAN;
      DELETE TOP (10) FROM #source
        OUTPUT DELETED.id
        INTO #target (id);
    COMMIT;
  END

--review results 
SELECT * FROM #source;
SELECT * FROM #target;

In the example, I set up a couple tables for the test. The WHILE loop checks if data exists in the source and then deletes a set of rows out and uses the OUTPUT clause to directly insert them into the new table.

This is a very lightweight operation because we do not need to check if the rows exist in the source table. We just delete a random set of rows until they are all moved. In reality, my batch size was 2 million - this should be adjusted accordingly.