SQL Server Partitioning: Managing Large Tables

On this page

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

Partition Function

SQL3 lines
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

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

Partitioned Table

SQL5 lines
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


Discover more from C4: Container, Code, Cloud & Context

Subscribe to get the latest posts sent to your email.

Written by

Nithin Mohan TK

Hello Namaste! 👋🏻 I'm an accomplished Enterprise Solution Architect & Engineering Leader with 20+ years of progressive experience architecting, building, and scaling transformative enterprise solutions. ⚜️ Solution Architect / AI & Cloud 📌 Galway, Ireland

Leave a comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.