Horizontal Partitioning — Database Scaling

Horizontal Partitioning means splitting a large table by rows while keeping the same table structure.

For example, instead of keeping 1 billion Orders rows together, we can divide them by year:

flowchart LR
    A[Orders Table] --> B[Horizontal Partitioning]

    B --> C[2024 Rows]
    B --> D[2025 Rows]
    B --> E[2026 Rows]
    B --> F[2027 Rows]

The application still sees one Orders table.

Why Horizontal Partitioning?

• Very large tables become easier to manage.
• Date-range queries can benefit from partition elimination.
• Old data can be archived independently.
• Partition-level maintenance becomes possible.
• Indexes can also be partitioned.

Real-World Example

Consider an e-commerce system with billions of orders.

Orders
├── 2024 → 300 million rows
├── 2025 → 400 million rows
├── 2026 → 500 million rows
└── 2027 → 200 million rows

A query for 2026 orders should not need to scan historical partitions unnecessarily.

SQL Server Complete Example

1. Create Database

CREATE DATABASE HorizontalPartitionDemo;
GO

USE HorizontalPartitionDemo;
GO

2. Create Partition Function

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

This creates four partitions:

Partition 1 → Before 2025
Partition 2 → 2025
Partition 3 → 2026
Partition 4 → 2027 and later

3. Create Partition Scheme

CREATE PARTITION SCHEME OrderDatePS
AS PARTITION OrderDatePF
ALL TO ([PRIMARY]);
GO

4. Create Orders Table

CREATE TABLE Orders
(
    OrderId INT NOT NULL,
    CustomerId INT NOT NULL,
    Amount DECIMAL(18,2) NOT NULL,
    OrderDate DATE NOT NULL,
    Status VARCHAR(20) NOT NULL,

    CONSTRAINT PK_Orders
        PRIMARY KEY CLUSTERED (OrderId, OrderDate)
)
ON OrderDatePS(OrderDate);
GO

5. Insert Data

INSERT INTO Orders
    (OrderId, CustomerId, Amount, OrderDate, Status)
VALUES
    (1, 101, 1000.00, '2024-05-10', 'Completed'),
    (2, 102, 1500.00, '2025-02-15', 'Completed'),
    (3, 103, 2000.00, '2025-09-20', 'Pending'),
    (4, 104, 3000.00, '2026-01-10', 'Completed'),
    (5, 105, 3500.00, '2026-08-25', 'Pending'),
    (6, 106, 4500.00, '2027-02-05', 'Completed');
GO

6. Find the Partition for Each Row

SELECT
    OrderId,
    OrderDate,
    $PARTITION.OrderDatePF(OrderDate) AS PartitionNumber
FROM Orders
ORDER BY OrderDate;
GO

Expected output:

OrderId  OrderDate    PartitionNumber
-------  ----------   ---------------
1        2024-05-10   1
2        2025-02-15   2
3        2025-09-20   2
4        2026-01-10   3
5        2026-08-25   3
6        2027-02-05   4

Query Only 2026 Data

SELECT
    OrderId,
    CustomerId,
    Amount,
    OrderDate,
    Status
FROM Orders
WHERE OrderDate >= '2026-01-01'
  AND OrderDate < '2027-01-01';
GO

Expected output:

OrderId  CustomerId  Amount   OrderDate    Status
-------  ----------  -------  ----------   ----------
4        104         3000.00  2026-01-10   Completed
5        105         3500.00  2026-08-25   Pending

SQL Server can use the date condition to eliminate partitions that cannot contain 2026 data.

flowchart TD
    A[2026 Query] --> B[Partition Function]

    B --> C[Partition 1
Skip]
    B --> D[Partition 2
Skip]
    B --> E[Partition 3
Scan]
    B --> F[Partition 4
Skip]

How Is It Different From Vertical Partitioning?

Horizontal Partitioning vs Sharding

flowchart LR
    A[Large Orders Data]

    A --> B[Partitioning]
    A --> C[Sharding]

    B --> D[One Database
Multiple Partitions]

    C --> E[Database 1]
    C --> F[Database 2]
    C --> G[Database 3]

Partitioning generally organizes data inside a database, whereas sharding distributes data across multiple database instances or servers.

When Should You Use It?

Horizontal partitioning is particularly useful when:

• The table contains hundreds of millions or billions of rows.
• Queries commonly filter on a suitable partition key.
• Data has a natural boundary such as date, tenant, or region.
• Historical data needs regular archiving.
• Large maintenance operations need to be isolated.

Interview Scenario

Interviewer: Your Orders table has 5 billion rows and most queries filter by OrderDate. What would you do?

Answer: I would consider horizontal partitioning using OrderDate as the partition key. In SQL Server, I can create a partition function and partition scheme and place the table on that scheme. Queries filtering by date can benefit from partition elimination. I would also review indexes, archiving, query patterns, and storage before deciding the final partition strategy.

Common Interview Questions

• What is horizontal partitioning?
• How does horizontal partitioning work?
• What is partition elimination?
• How do you implement horizontal partitioning in SQL Server?
• What is a partition function?
• What is a partition scheme?
• Horizontal vs vertical partitioning?
• Horizontal partitioning vs sharding?
• What should you use as a partition key?
• Does partitioning automatically improve query performance?

Key Takeaways

• Horizontal partitioning = splitting rows.
• The table structure remains the same.
• Date is a common partition key for transactional data.
• SQL Server uses partition functions and partition schemes.
• Partition elimination can reduce unnecessary scanning.
• Partitioning is different from sharding.
• A good partition key should match the application's access pattern.