SQL Server Partitioning: Managing Large Tables

Table partitioning splits large tables into smaller, manageable pieces while appearing as one table. Essential for billion-row tables.

Partition Function

CREATE PARTITION FUNCTION pf_OrderDate (datetime)
AS RANGE RIGHT FOR VALUES 
('2019-01-01', '2019-04-01', '2019-07-01', '2019-10-01');

Partition Scheme

CREATE PARTITION SCHEME ps_OrderDate
AS PARTITION pf_OrderDate
TO (fg_2018, fg_Q1, fg_Q2, fg_Q3, fg_Q4);

Partitioned Table

CREATE TABLE Orders (
    OrderId INT,
    OrderDate DATETIME,
    CustomerId INT
) ON ps_OrderDate(OrderDate);

Benefits

• Fast archival with partition switching
• Partition elimination for queries
• Per-partition maintenance

References

• Partitioned Tables