SQL Server › Partitioning

What is the difference between a partition function and a partition scheme in SQL Server?

Beginner

Answer

A partition function defines the actual boundary values used to split data into ranges, such as splitting by year, while a partition scheme maps each of those defined ranges to a specific physical filegroup, letting you control exactly where each partition's data is physically stored on disk.

CREATE PARTITION FUNCTION SalesDateRange (DATE)
AS RANGE RIGHT FOR VALUES ('2025-01-01', '2026-01-01');

CREATE PARTITION SCHEME SalesPartitionScheme
AS PARTITION SalesDateRange
TO ([FG2024], [FG2025], [FG2026]);

Real-world example A company stores older sales partitions on slower, cheaper storage and recent partitions on faster storage by mapping each partition range to a different filegroup through a well designed partition scheme.

Common follow-ups: Can multiple tables share the same partition function and scheme?;How do you decide which filegroup to place each partition on?

Backup & Recovery;SQL Server Architecture & Editions