Table Partitioning

Imagine an innings table holding every innings of every IPL season — hundreds of millions of rows. Most queries only care about one season, yet every query, index rebuild and backup has to deal with the whole monster. Partitioning cuts it into smaller pieces (one per season) while your queries still just say FROM innings.

Definition

Partitioning divides one large table (or index) into smaller, separately stored pieces called partitions, based on the value of a partition key column, while the table is still treated as ONE logical table by every query.

• Partition key — the column that decides which partition a row belongs to, e.g. match_date or region.
• Separately stored — each partition can live in its own filegroup, even on a different disk.
• One logical table — no query changes: SELECT … FROM innings WHERE … works exactly as before.

Why do we need partitions?

The 4 building blocks

Finally you create the table ON the partition scheme, naming the partition key column.

Partition function — RANGE LEFT vs RANGE RIGHT

Boundaries 2022-12-31 | 2023-12-31 | 2024-12-31 → 3 boundaries make 4 partitions:

RANGE LEFT — a boundary value belongs to the partition on its LEFT (2023-12-31 stays in partition 2). RANGE RIGHT — a boundary value starts the partition on its RIGHT (2023-12-31 would start partition 3).

Filegroups and data files

A filegroup is a logical container of one or more data files. Data files (.ndf) are the physical files on disk.

Each partition can live in its own filegroup (even on different disks); a filegroup can hold several data files.

Syntax — full example

-- 1. Partition function: the boundaries
CREATE PARTITION FUNCTION pf_season (DATE)
AS RANGE LEFT FOR VALUES ('2022-12-31', '2023-12-31', '2024-12-31');

-- 2 + 3. Filegroups and data files (repeat for FG_2023, FG_2024, FG_2025)
ALTER DATABASE IPL ADD FILEGROUP FG_2022;
ALTER DATABASE IPL ADD FILE
    (NAME = 'ipl_2022', FILENAME = 'D:\SQLData\ipl_2022.ndf')
    TO FILEGROUP FG_2022;

-- 4. Partition scheme: partition -> filegroup
CREATE PARTITION SCHEME ps_season
AS PARTITION pf_season
TO (FG_2022, FG_2023, FG_2024, FG_2025);

-- Table stored ON the scheme, using match_date as the partition key
CREATE TABLE innings (
    id         INT,
    player     VARCHAR(50),
    match_date DATE,
    runs       INT
) ON ps_season (match_date);

-- Which partition does each row live in? How many rows per partition?
SELECT $PARTITION.pf_season(match_date) AS partition_no, COUNT(*) AS rows_in_partition
FROM innings
GROUP BY $PARTITION.pf_season(match_date);

SELECT partition_number, rows
FROM sys.partitions
WHERE object_id = OBJECT_ID('innings');

Example — simulate RANGE LEFT vs RANGE RIGHT (playground)

The playground cannot partition tables, but a CASE expression can show exactly which partition each row would go to:

Only the row that falls exactly on a boundary differs: LEFT keeps it in partition 2, RIGHT moves it to partition 3. (Run the first example below to see the query.)

Partitioning + indexing

For very large fact tables, the best option is both: partition by season and add a columnstore index. Each partition gets its own portion of the index, so:

• Partition = read only the season you need.
• Columnstore = compress and scan fast inside it.
• You can rebuild one season at a time.

Common mistakes

• ❌ Partitioning a small table "for speed". ✅ Partitioning is for very large tables (tens of millions of rows+); on small tables it only adds complexity.
• ❌ Filtering on a different column than the partition key. ✅ Partition elimination only happens when the WHERE uses the partition key.
• ❌ Getting LEFT/RIGHT wrong for dates. ✅ With RANGE LEFT use the last day of each period (2023-12-31); with RANGE RIGHT use the first day (2024-01-01).
• ❌ Forgetting that N boundaries make N + 1 partitions (and need N + 1 filegroups in the scheme).

Interview tip

Q: Does partitioning always make queries faster? A: No. It helps when queries filter on the partition key (partition elimination) and with maintenance and archiving. Queries that don't filter on the key may even get slower because they must touch every partition. Its biggest wins are often manageability: per-partition rebuilds and instant switch-in/switch-out.