SQL Server: Table Partitioning

Atlas now supports SQL Server partitioning - partition_function, partition_scheme, and partitioned tables - across inspect, diff, and migrate. Value-only boundary changes are diffed into in-place SPLIT RANGE / MERGE RANGE operations instead of a destructive recreate, and are fully reversible via migrate down.

Partition functions, partition schemes, and partitioned tables are supported across the full workflow: schema inspect, schema diff, migrate diff, migrate apply, and migrate down.

New HCL Objects

Three new constructs were introduced:

• partition_function - an input parameter type (SQL Server allows exactly one), a range of LEFT (default, omitted in HCL) or RIGHT, and ordered values (boundary values as string literals).
• partition_scheme - a partition reference to a partition_function, and a filegroups list (length must be len(values) + 1). When all entries are identical, Atlas emits the compact ALL TO ([...]) form.
• table.partition - a scheme reference and the partitioning column.

A Full Partitioned Table

Starting from this SQL state:

CREATE PARTITION FUNCTION [PF1] (int) AS RANGE LEFT FOR VALUES (100, 200, 300);
CREATE PARTITION SCHEME [PS1] AS PARTITION [PF1] ALL TO ([PRIMARY]);
CREATE TABLE [dbo].[Orders] (
  [OrderID]   int NOT NULL,
  [OrderDate] int NOT NULL,
  CONSTRAINT [PK_Orders] PRIMARY KEY ([OrderID] ASC, [OrderDate] ASC)
) ON [PS1]([OrderDate]);

Atlas inspects it into HCL:

partition_function "PF1" {
  input  = int
  values = ["100", "200", "300"]
}
partition_scheme "PS1" {
  partition  = partition_function.PF1
  filegroups = ["PRIMARY", "PRIMARY", "PRIMARY", "PRIMARY"]
}
table "Orders" {
  schema = schema.dbo
  column "OrderID"   { null = false, type = int }
  column "OrderDate" { null = false, type = int }
  primary_key { columns = [column.OrderID, column.OrderDate] }
  partition {
    scheme = partition_scheme.PS1
    column = column.OrderDate
  }
}

migrate diff orders creation automatically (function -> scheme -> table), and drops in reverse dependency order (table -> scheme -> function):

-- Create partition function "PF1"
CREATE PARTITION FUNCTION [PF1] (int) AS RANGE FOR VALUES (100, 200, 300);
-- Create partition scheme "PS1"
CREATE PARTITION SCHEME [PS1] AS PARTITION [PF1] ALL TO ([PRIMARY]);
-- Create "Orders" table
CREATE TABLE [dbo].[Orders] ( ... ) ON [PS1] ([OrderDate]);

RANGE RIGHT

range = RIGHT is preserved on round-trip and emitted explicitly, whereas the default LEFT is omitted:

partition_function "PF2" {
  input  = int
  range  = RIGHT
  values = ["100", "200", "300"]
}

CREATE PARTITION FUNCTION [PF2] (int) AS RANGE RIGHT FOR VALUES (100, 200, 300);

Smart Diffing: SPLIT / MERGE Instead of Recreate

The headline capability: value-only changes to a partition function are translated into in-place SPLIT RANGE / MERGE RANGE operations rather than a destructive drop and recreate.

Adding a boundary value (SPLIT) - here adding 400. Each split consumes a filegroup, so Atlas emits ALTER PARTITION SCHEME ... NEXT USED before each split:

-- Set next used filegroup for split on "PS1"
ALTER PARTITION SCHEME [PS1] NEXT USED [PRIMARY];
-- Split partition boundary 400 on "PF1"
ALTER PARTITION FUNCTION [PF1]() SPLIT RANGE (400);

Removing a boundary value (MERGE) - here removing 200:

-- Merge partition boundary 200 from "PF1"
ALTER PARTITION FUNCTION [PF1]() MERGE RANGE (200);

Structural changes (changing the input type or the RANGE direction) cannot be done in place and fall back to drop and recreate.

migrate down Reverses SPLIT / MERGE

Down migrations invert the operations - a MERGE is reverted with a SPLIT, and a SPLIT with a MERGE:

-- Revert a MERGE -> re-add the boundary
ALTER PARTITION SCHEME [PS1] NEXT USED [PRIMARY];
ALTER PARTITION FUNCTION [PF1]() SPLIT RANGE (200);
-- Revert a SPLIT -> remove the boundary
ALTER PARTITION FUNCTION [PF1]() MERGE RANGE (400);

Filegroup-Only Scheme Changes Require Recreate

Changing only a scheme's filegroup mapping (e.g. moving a partition back to PRIMARY) can't be altered in place - SQL Server requires dropping and recreating the scheme and its dependent table:

-- Drop "Orders" table
DROP TABLE [dbo].[Orders];
-- Drop partition scheme "PS1"
DROP PARTITION SCHEME [PS1];
-- Create partition scheme "PS1"
CREATE PARTITION SCHEME [PS1] AS PARTITION [PF1] ALL TO ([PRIMARY]);
-- Create "Orders" table
CREATE TABLE [dbo].[Orders] ( ... ) ON [PS1] ([Amount]);

Interop with Alias Types (UDTs)

Partition objects coexist with alias types (UDTs). A table can use an alias-typed column alongside a partitioned column, and inspect resolves both correctly in the same schema with a clean HCL round-trip:

CREATE TYPE [dbo].[OrderID] FROM int NOT NULL;
CREATE PARTITION FUNCTION [PF_UDT] (int) AS RANGE LEFT FOR VALUES (100, 200, 300);
CREATE PARTITION SCHEME [PS_UDT] AS PARTITION [PF_UDT] ALL TO ([PRIMARY]);
CREATE TABLE [dbo].[Orders] (
  [id]    [dbo].[OrderID] NOT NULL,
  [month] int NOT NULL,
  CONSTRAINT [PK_Orders] PRIMARY KEY ([id] ASC, [month] ASC)
) ON [PS_UDT]([month]);

Read more about defining partitioned tables in HCL in the SQL Server partitions documentation.