A partitioned columnstore with no downtime on Standard edition

Compressing and partitioning a 385 million row table written continuously, on SQL Server 2022 Standard: new table built alongside, copy trigger, a partition boundary for the day, a 2.2 second cutover, and what went wrong.

Co-written with Claude, who wrote the migration scripts with me and replayed the method in the lab.

The starting table

The table received the measurements of a monitoring chain: a collector wrote to it continuously, about 850,000 rows per day, around ten inserts per second, and dashboards read from it. It had never been purged or compressed: 385 million rows, 50.4 GB, a clustered index on an identifier and three nonclustered indexes, none of them compressed. There was no maintenance window at all.

The goal: a compressed table, partitioned by year so that a whole year can be purged by a partition TRUNCATE once the retention is decided, and that stays readable and writable during the whole operation.

One detail of its history mattered. The table had been recreated eleven months earlier and a whole year reloaded out of order: identifier 1 had a December date, while the oldest row was from May. To find the start of the history, you need MIN() on the timestamp, not the timestamp of the smallest identifier. I made that mistake once, the same day, before correcting it.

The constraints of Standard edition

The server ran SQL Server 2022 Standard. This edition has neither online index operations nor parallel index operations (Editions and supported features of SQL Server 2022, High availability and Manageability tables). Partitioning the table in place or rebuilding it as a columnstore therefore takes a schema modification lock (Sch-M) on the whole table for the whole operation; creating a nonclustered index takes a shared lock (S), which allows reads but blocks all writes (CREATE INDEX, ONLINE option). In both cases, whatever the LOCK_ESCALATION setting. On a 50 GB table, that means tens of minutes during which no insert can get through.

A partitioned columnstore adds rules of its own, which I checked in the lab on SQL Server 2022 (16.0.4265.3):

  • a SPLIT is only accepted on an empty partition (error 35346);
  • a MERGE is refused when both partitions contain data (error 35344);
  • a TRUNCATE TABLE ... WITH (PARTITIONS (...)) works, provided every index is aligned with the partition scheme (TRUNCATE TABLE); on a partitioned clustered columnstore, the engine refuses a nonclustered index that is not aligned anyway (error 35322). The list of engine errors still contains 35349 (“A table with a columnstore index cannot be truncated”), which I did not manage to trigger: in the lab, truncating one partition of a clustered columnstore with an aligned B-tree index took 4 ms.

Partition boundaries must therefore be in place before the data arrives. A boundary forgotten on a filled partition can only be added after emptying that partition with a SWITCH, or by dropping and recreating the columnstore, which amounts to rebuilding the whole table offline.

The new table, built alongside

The idea is never to touch the structure of the table in service. I build a new table, already partitioned and in columnstore, fill it while the old one keeps serving, then swap the names.

The examples below replay the method on the Elearning.Activite table of the demo database (7.8 million rows, from 2018 to 2026). ContactId plays the part of the site, DateHeure that of the timestamp.

The partition function and boundary J

One partition per year, plus one more boundary, J, the midnight that follows the creation of the copy trigger. The history copy only starts after J: from then on, everything the trigger writes lands after J, everything the copy writes lands before it, and the two streams never write to the same partition, as long as measurements arrive with the current timestamp: a late measurement, dated before J, would land with the history.

At the client, I had set it up so that a lock escalation by the copy, stopped at its partition, would not block the trigger. In the lab, the precaution turned out to be unnecessary: during an open copy transaction, bulk loaded or in the deltastore, an insert into the same partition goes through in 4 ms, and the copy’s escalation never goes above the partition.

USE PachaDataFormation;
GO

-- One partition per year, plus a boundary J, the midnight that follows the creation of the trigger
CREATE PARTITION FUNCTION pfActiviteAnnee (datetime2(0))
AS RANGE RIGHT FOR VALUES ('2019-01-01', '2020-01-01', '2021-01-01', '2022-01-01',
                           '2023-01-01', '2024-01-01', '2025-01-01', '2026-01-01',
                           '2026-11-21',   -- J
                           '2027-01-01', '2028-01-01');

CREATE PARTITION SCHEME psActiviteAnnee
AS PARTITION pfActiviteAnnee ALL TO ([PRIMARY]);
GO

Boundary J then stays in the function: the partitions before and after J both contain data, and the columnstore refuses to merge them. They will go away together when the year is purged, after both are truncated.

The columnstore table

USE PachaDataFormation;
GO

-- The new table, same columns, partitioned clustered columnstore
CREATE TABLE Elearning.ActiviteNouvelle (
    ActiviteId      bigint       NOT NULL,
    DateHeure       datetime2(0) NOT NULL,
    ContactId       int          NOT NULL,
    InscriptionId   int          NOT NULL,
    ModuleId        int          NULL,
    TypeEvenementCd char(4)      NOT NULL,
    Etape           smallint     NULL,
    DureeSecondes   int          NULL,
    Score           tinyint      NULL,
    Appareil        char(1)      NOT NULL
) ON psActiviteAnnee (DateHeure);

CREATE CLUSTERED COLUMNSTORE INDEX ccix_ActiviteNouvelle
ON Elearning.ActiviteNouvelle
ON psActiviteAnnee (DateHeure);

-- Lock escalation to the partition, not to the table
ALTER TABLE Elearning.ActiviteNouvelle SET (LOCK_ESCALATION = AUTO);

-- Constraints are recreated under another name, unique in the schema
ALTER TABLE Elearning.ActiviteNouvelle ADD CONSTRAINT chk_ActiviteNouvelle_Appareil
    CHECK (Appareil IN ('M', 'T', 'O'));

-- Migration checkpoints
CREATE TABLE Elearning.ActiviteMigration (
    Repere sysname NOT NULL PRIMARY KEY,
    Valeur bigint  NOT NULL
);
GO

With LOCK_ESCALATION = AUTO, an escalation on a partitioned table stops at the partition (HoBT) level and does not go up to the table (ALTER TABLE, LOCK_ESCALATION option).

At the client, the original table had a primary key that started with the identifier. I dropped it, after checking that nothing used it: no foreign key pointed to the table, and of the 8,745 Query Store queries that referenced the table over a month, none read the identifier. On a partitioned columnstore, a unique key has to include the partitioning column anyway: every nonclustered index there is aligned, and an aligned unique index must contain that column (error 1908). The other constraints are recreated on the new table, under another name as long as the old one exists: the example only recreates chk_Activite_Appareil; the four foreign keys of the demo table are recreated the same way, before the cutover.

The copy trigger

USE PachaDataFormation;
GO

CREATE TRIGGER Elearning.trg_Activite_Recopie
ON Elearning.Activite
WITH EXECUTE AS OWNER   -- useful with SET IDENTITY_INSERT, which requires ALTER on the table
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    IF NOT EXISTS (SELECT 1 FROM inserted) RETURN;

    INSERT INTO Elearning.ActiviteNouvelle
        (ActiviteId, DateHeure, ContactId, InscriptionId, ModuleId,
         TypeEvenementCd, Etape, DureeSecondes, Score, Appareil)
    SELECT ActiviteId, DateHeure, ContactId, InscriptionId, ModuleId,
           TypeEvenementCd, Etape, DureeSecondes, Score, Appareil
    FROM inserted;
END;
GO

At the client, the identifier was an IDENTITY column: the trigger wrapped the insert in SET IDENTITY_INSERT ... ON and OFF to copy the original value. That statement requires the ALTER permission on the table, which the collector’s login did not have, hence WITH EXECUTE AS OWNER.

CREATE TRIGGER waits for its Sch-M lock: the inserts in flight finish first, and every later one goes through the trigger. So there is no gap between the history and what the trigger copies. While it waits, new inserts queue up behind it: a long transaction open on the table therefore blocks the collector. A bulk load (BULK INSERT, bcp) does not fire triggers without the FIRE_TRIGGERS option (BULK INSERT): check how the collector writes. Here, it went through an insert procedure.

The risk lies elsewhere: an error in the trigger rolls back the collector’s insert. At the client, with the same constraints and the same disk on both tables, that was unlikely, and the trigger could be dropped at any time without touching the old table.

Copying the history in batches

USE PachaDataFormation;
GO
SET NOCOUNT ON;

-- Last identifier to copy, fixed on the first run: the copy can be restarted safely
IF NOT EXISTS (SELECT 1 FROM Elearning.ActiviteMigration WHERE Repere = N'fin_copie')
    INSERT INTO Elearning.ActiviteMigration (Repere, Valeur)
    SELECT N'fin_copie',
           ISNULL((SELECT MIN(ActiviteId) - 1 FROM Elearning.ActiviteNouvelle),
                  (SELECT MAX(ActiviteId) FROM Elearning.Activite));

DECLARE @lot bigint = 4194304;   -- four full rowgroups, if the batch falls within a single partition
DECLARE @fin bigint = (SELECT Valeur FROM Elearning.ActiviteMigration WHERE Repere = N'fin_copie');
DECLARE @de  bigint = ISNULL((SELECT MAX(ActiviteId) FROM Elearning.ActiviteNouvelle
                              WHERE ActiviteId <= @fin), 0);
DECLARE @a bigint;

WHILE @de < @fin
BEGIN
    SET @a = CASE WHEN @fin - @de > @lot THEN @de + @lot ELSE @fin END;

    -- No TABLOCK: it would block the trigger's inserts
    INSERT INTO Elearning.ActiviteNouvelle
        (ActiviteId, DateHeure, ContactId, InscriptionId, ModuleId,
         TypeEvenementCd, Etape, DureeSecondes, Score, Appareil)
    SELECT ActiviteId, DateHeure, ContactId, InscriptionId, ModuleId,
           TypeEvenementCd, Etape, DureeSecondes, Score, Appareil
    FROM Elearning.Activite
    WHERE ActiviteId > @de AND ActiviteId <= @a
    OPTION (RECOMPILE);   -- estimates from the real batch bounds, see below

    RAISERROR(N'%I64d to %I64d: %d rows', 0, 1, @de, @a, @@ROWCOUNT) WITH NOWAIT;
    SET @de = @a;
END;
GO

The end boundary is the first identifier copied by the trigger, minus one. It is frozen when the copy first runs, at the client hours after the trigger went in: right after the trigger is created, two concurrent inserts can commit out of order, and a row would be copied twice. On the demo database, where no collector writes, the trigger has copied nothing, and the copy takes everything that exists.

According to the documentation, an INSERT ... SELECT of at least 102,400 rows into a columnstore is a bulk load: the rows go straight into compressed rowgroups, without going through the deltastore, even without TABLOCK (Columnstore indexes: data loading guidance). On a partitioned table, that threshold applies partition by partition. TABLOCK would make the insert parallel, but it would take an exclusive lock on the table and block the trigger. A batch of 4,194,304 consecutive identifiers that fall within a single partition gives four full rowgroups of 1,048,576 rows, if memory and the dictionary do not trim them; on the demo database, where each batch spans several years, no rowgroup is full.

At the client, the copy ran in a SQL Server Agent job, if it was stopped, it only had to be restarted to resume from the last committed batch. It copied 385.1 million rows in 1,786 s, half an hour, about 216,000 rows per second. No session was blocked, and the log (database in the simple recovery model) never went above 0.1% full. The rowgroups averaged 1,044,000 rows.

Departures from the plan

The plan had five steps. Most of the departures below added one.

The start date of the copy

The start of the copy was brought forward by two days, and boundary J with it. Boundary J had been set in the future, and the “before J” partition already held the trigger’s rows: the boundary could no longer be moved, since a columnstore refuses to split a filled partition. I dropped the trigger and the new table. The partition function, which no table used any more, changed instantly, and then I recreated everything. So boundary J goes in at the moment the trigger does, not before.

Queries on the columnstore alone

Before the cutover, I ran the five most expensive dashboard queries from Query Store against both tables, three runs per table.

QueryOld table (B-tree)Columnstore alone
Availability of one site over the whole history0.76 to 1.0 s20.5 to 25.6 s
Availability over 7 and 14 days, by suffix0.76 to 0.95 s1.9 to 14.7 s
Availability over 7 and 14 days, by site0.72 to 0.95 s1.1 to 1.8 s
Availability and outages, 7 and 14 days0.72 to 0.74 s1.3 to 1.4 s
Zero-value periods over 7 days0.38 to 0.40 s0.17 to 0.27 s

They all filter on a site. But every rowgroup contains every site, since the rows arrive in time order: the minimum and maximum site values of each segment cover the whole range, and no rowgroup can be skipped on that criterion. Only the zero-value periods query wins: it reads the fewest rows, over seven days and a single suffix. I did not isolate the cause. On Standard edition, batch mode is also limited to two threads (Editions and supported features of SQL Server 2022).

Without that measurement, I would have discovered the regression in production.

The B-tree index

It took a nonclustered B-tree index on the site and the timestamp, aligned with the partition scheme (the partition TRUNCATE requires it) and with PAGE compression.

USE PachaDataFormation;
GO

-- B-tree aligned with the partitions, PAGE compression
CREATE NONCLUSTERED INDEX nix_ActiviteNouvelle_Contact_DateHeure
ON Elearning.ActiviteNouvelle (ContactId, DateHeure)
INCLUDE (TypeEvenementCd, DureeSecondes)
WITH (DATA_COMPRESSION = PAGE)
ON psActiviteAnnee (DateHeure);
GO

On Standard edition, this build is offline and serial: a shared lock (S) on the new table for half an hour, which allows reads but blocks all inserts. With the trigger in place, every collector insert would have waited behind it, since the trigger writes to that table. So I dropped the trigger, built the index on a table that nothing wrote to any more, and planned to catch up at the cutover on the rows that had arrived in the meantime in the old table, which remained complete.

The first index build

The first index build was lost after about 25 minutes out of 33: the SSMS connection on the workstation was cut (error 10053, “An established connection was aborted by the software in your host machine”), and SQL Server rolled the build back, since its session no longer existed. The second one ran in an Agent job. Since then, a long operation in production goes into an Agent job, even when I stay in front of the screen.

A forced plan in Query Store

While reading Query Store, I found a forced plan on one of the dashboard queries that had been failing for eleven months: 268 failures, reason NO_INDEX, and a last execution on the day the table had been recreated. The index the plan used had been gone since the table’s DROP and CREATE, and nobody had noticed. The sys.query_store_plan view shows these failures in force_failure_count and last_force_failure_reason_desc.

The cutover

USE PachaDataFormation;
GO
SET XACT_ABORT ON;
SET LOCK_TIMEOUT 5000;   -- if the lock is not obtained within 5 s, everything is rolled back: just rerun

-- No cutover if the history copy did not reach its end boundary
DECLARE @fin bigint = (SELECT Valeur FROM Elearning.ActiviteMigration WHERE Repere = N'fin_copie');
IF (SELECT MAX(ActiviteId) FROM Elearning.ActiviteNouvelle WHERE ActiviteId <= @fin)
   IS DISTINCT FROM (SELECT MAX(ActiviteId) FROM Elearning.Activite WHERE ActiviteId <= @fin)
    THROW 50000, N'History copy incomplete: no cutover', 1;

BEGIN TRANSACTION;

    -- No more inserts into the old table until COMMIT
    SELECT TOP (0) ActiviteId FROM Elearning.Activite WITH (TABLOCKX, HOLDLOCK);

    -- The trigger, if it is still there
    DROP TRIGGER IF EXISTS Elearning.trg_Activite_Recopie;

    -- Catch up on the rows that arrived since the trigger was dropped
    INSERT INTO Elearning.ActiviteNouvelle
        (ActiviteId, DateHeure, ContactId, InscriptionId, ModuleId,
         TypeEvenementCd, Etape, DureeSecondes, Score, Appareil)
    SELECT a.ActiviteId, a.DateHeure, a.ContactId, a.InscriptionId, a.ModuleId,
           a.TypeEvenementCd, a.Etape, a.DureeSecondes, a.Score, a.Appareil
    FROM Elearning.Activite AS a
    WHERE a.ActiviteId > (SELECT MAX(ActiviteId) FROM Elearning.ActiviteNouvelle);

    EXEC sp_rename N'Elearning.Activite', N'ActiviteAncienne';
    EXEC sp_rename N'Elearning.ActiviteNouvelle', N'Activite';

COMMIT TRANSACTION;
GO

At the client, the transaction lasted 2.2 s under an exclusive lock, catch-up included: 103,869 rows, which had arrived during the three hours since the trigger was dropped. The collector was not stopped; its inserts waited those 2.2 s.

The catch-up assumes increasing identifiers and a finished history copy: if the copy had stopped along the way, MAX(ActiviteId) would silently skip the gap. The check at the top of the block then refuses the cutover; it only compares the maxima, so the next day’s count is still needed.

As a precaution, in case an insert blocked during the cutover landed in the old table once the lock is released, I reseeded the identity of the new table (DBCC CHECKIDENT ... RESEED) inside the transaction, before the COMMIT, with a gap of 10,000, so that such a row could be copied over with its identifier without a collision, then looked for those stray rows a minute later: there were none. Neither were there in the lab: the blocked inserts, ad hoc or through a procedure, all arrived in the new table. Catching up on an IDENTITY table is also wrapped in SET IDENTITY_INSERT ... ON and OFF. The views that reference the table by name keep their column metadata: sp_refreshview on each of them, and sp_recompile on the insert procedure.

The next day, the check gave 385,263,633 rows in the old table, and as many in the new one up to the last identifier before the cutover.

Checking the rowgroups

Once the cutover is done, read the state of the rowgroups again, partition by partition.

USE PachaDataFormation;
GO

SELECT rg.partition_number, rg.state_desc, rg.trim_reason_desc,
       COUNT(*)           AS rowgroups,
       SUM(rg.total_rows) AS total_rows,
       MIN(rg.total_rows) AS smallest
FROM sys.dm_db_column_store_row_group_physical_stats AS rg
WHERE rg.object_id = OBJECT_ID(N'Elearning.Activite')
GROUP BY rg.partition_number, rg.state_desc, rg.trim_reason_desc
ORDER BY rg.partition_number, rg.state_desc;
GO

At the client, the history fitted in 369 compressed rowgroups averaging 1,044,000 rows: 354 full (reason NO_TRIM), 15 trimmed at the end of a batch (BULKLOAD), none trimmed for memory or dictionary size, no deleted rows. The 103,869 catch-up rows, above the 102,400 threshold, arrived already compressed. The collector’s inserts filled several open delta rowgroups at once, which happens with concurrent sessions: that is normal, and each will close at 1,048,576 rows.

One rowgroup was a problem: an OPEN delta rowgroup of 28,471 rows, written by the trigger between its creation and time J, hence into the partition before the boundary. That partition will receive nothing more, and the tuple mover only compresses closed (CLOSED) rowgroups: the rowgroup could stay open for a long time. The documentation mentions a background merge task that, since SQL Server 2019, also compresses small open rowgroups that have existed for some time, according to an internal threshold (Columnstore indexes: overview); I did not wait for it.

This happens whenever a temporary stream (a trigger, a catch-up) writes fewer than 102,400 rows into a partition that receives nothing more.

It also happens, in the lab, with the copy itself. Without OPTION (RECOMPILE), the copy loop leaves open rowgroups in several partitions, of a size that varies from one run to the next (from 130 to 308,947 rows across the trials), including in partitions that received several hundred thousand rows from the same INSERT. With OPTION (RECOMPILE), which gives the optimizer the real bounds of the batch instead of variables, no trial left any. I did not find this behavior in the documentation, which announces compressed rowgroups for any batch of at least 102,400 rows.

The fix is a REORGANIZE of the partition, which runs online, even on Standard edition (ALTER INDEX, REORGANIZE of a columnstore index):

USE PachaDataFormation;
GO

-- Compresses the open and closed rowgroups of partition 6
ALTER INDEX ccix_ActiviteNouvelle ON Elearning.Activite
REORGANIZE PARTITION = 6 WITH (COMPRESS_ALL_ROW_GROUPS = ON);

-- Then merges the small compressed rowgroups
ALTER INDEX ccix_ActiviteNouvelle ON Elearning.Activite
REORGANIZE PARTITION = 6;
GO

The sp_rename of the cutover does not rename the indexes: this one keeps the name it had on Elearning.ActiviteNouvelle. The partition number is the one the check query gives.

At the client, the first REORGANIZE, with COMPRESS_ALL_ROW_GROUPS, took one second. It compressed the delta rowgroup (transition REORG_FORCED, 28,471 rows) and, along the way, merged the two smallest compressed rowgroups of the partition (298,759 and 561,733 rows) into one of 860,492 (transition MERGE). But it did not merge the new rowgroup of 28,471 rows: that took a second REORGANIZE, which combined it with the 860,492 row group into one of 888,963 rows. The partition ends up with 237 compressed rowgroups, the smallest holding 888,963 rows. The replaced rowgroups go to TOMBSTONE, and the tuple mover cleans them up afterward.

The lab gives the same result. In one partition of the demo database, after a copy without OPTION (RECOMPILE), the first REORGANIZE compressed an open rowgroup of 21,195 rows without merging it, and the second combined it with the 878,709 row group. With one difference: the dictionary capped the merged rowgroup at 885,464 rows (reason DICTIONARY_SIZE), leaving a rowgroup of 14,440 rows.

The results

Space

BeforeAfter
Table50.4 GB, 4 uncompressed indexes9.3 GB
of which data14.1 GB (clustered index)2.6 GB (columnstore)
of which nonclustered indexes36.2 GB (3 indexes)6.7 GB (1 index, PAGE)

Once the old table is dropped, its 50 GB will stay allocated to the data file, which does not shrink without DBCC SHRINKFILE.

Queries

Same protocol as before the cutover, on the new indexed table:

QueryOld tableNew table
Availability over 7 and 14 days, by site0.73 s0.04 s
Availability and outages, 7 and 14 days0.72 s0.06 s
Zero-value periods over 7 days0.39 s0.04 to 0.06 s
Availability over 7 and 14 days, by suffix0.74 s1.08 s
Availability of one site over the whole history0.78 s1.23 s

The two queries that lose about 0.4 s are the only two that send the site as nvarchar, while the column is varchar. In the lab, with a Windows collation and an index on the site and the timestamp, an nvarchar constant keeps the seek, but on the site alone: the timestamp becomes a residual filter, and the query reads the site’s whole history (20,000 rows read for 202 returned, against 202 with a varchar constant). The by-suffix query, which only asks for 7 and 14 days, therefore probably reads the whole history, like the other one. I did not check this on the client’s actual plan. What remains is the gap between the two tables for a read of a site’s whole history: the new one has to decompress the PAGE pages of the index, a hypothesis I did not isolate. I accepted the loss: the other 7- and 14-day queries, by far the most frequent, are 6 to 18 times faster, and one more narrow index would have cost 2 to 3 GB (estimate) and another half-hour offline build.

The next morning, Query Store on the queries actually sent by the dashboards, about fifty of them (before: the week before the migration; after: from the cutover to 9:45 the next morning, 1 to 6 executions per query, so the figures are indicative):

Query familyBeforeAfterLogical reads before, after
7- and 14-day panels (the vast majority)0.6 to 1.8 s20 to 65 ms11,700 to 27,000, 92 to 206
Simple count panels0.5 to 0.9 s1 to 3 ms11,700 to 28,000, 23 to 1,005
Availability over a site’s whole history0.74 to 1.5 s0.85 to 1.05 s7,700 to 9,200, 5,500 to 6,500

The whole-history query, one of the two the previous day’s measurement showed as slower, stays at the same level in production. The query whose forced plan had been failing for eleven months goes from 1.2 s to 43 ms.

Along the way: the dashboards send the site as nvarchar, while the column is varchar. The conversion costs about a third of the query time on both tables (0.76 s versus 0.53 s on the old one, 1.23 s versus 0.88 s on the new one). The seek remains possible with a Windows collation, but on the site alone; with a SQL collation, the conversion prevents it. The fix belongs on the dashboard side.

The collector’s inserts

Hourly averages from Query Store:

PeriodAverage durationLogical readsLog per insert
Old table, no trigger123 to 143 µs19.2626 bytes
Old table, trigger active (copy running)318 to 334 µs24 to 25770 to 790 bytes
New table, first hour157 to 164 µs8.0361 to 371 bytes

The trigger multiplied the cost of an insert by about 2.4 during the migration, without ever blocking the collector: the maximum observed is 347 ms, during the hour of the copy. On the new table, an insert reads 2.4 times fewer pages and writes 40% less log, since there is only one B-tree index to maintain instead of four. It does take about 20% longer (columnstore delta rowgroup and PAGE compression of the index). At ten inserts per second, the difference is in the order of 0.3 ms per second.

Before dropping the old table

The old table stays a few days under another name, to allow a rollback: copy back what arrived since the cutover, then swap the names again. Before dropping it, I check that a full backup taken after the cutover exists and contains both tables, and I note its retention. Here, the full backup of the following night (59.9 GB, 17.8 GB compressed, with checksums) was only kept about 48 hours on disk, and the database was in the simple recovery model: to keep the old table longer, that file has to be copied elsewhere.

Finally, every year, add the boundary for year N+2 while the last partition is still empty: with a columnstore, a forgotten boundary can no longer be added with a simple SPLIT.