PartitionedTableExample.sql

/*
Purpose:
- Demonstrate a small, self-contained partitioned table keyed by an integer identifier.

Safety:
- Changes state by creating a partition function, scheme, table, and indexes.
- Run only in a disposable database or after renaming every sample object.

Requirements:
- SQL Server 2012 or later.

Customization:
- Review boundary values, filegroup placement, key type, and index design.
*/

CREATE PARTITION FUNCTION SampleEventIdPartitionFunction (INT)
AS RANGE RIGHT FOR VALUES (10000, 20000, 30000, 40000);
GO

CREATE PARTITION SCHEME SampleEventIdPartitionScheme
AS PARTITION SampleEventIdPartitionFunction
ALL TO ([PRIMARY]);
GO

CREATE TABLE dbo.SampleEvents
(
    event_id INT NOT NULL,
    occurred_at DATETIME2(0) NOT NULL,
    payload NVARCHAR(4000) NULL,
    CONSTRAINT PK_SampleEvents
        PRIMARY KEY NONCLUSTERED (event_id)
        ON [PRIMARY]
)
ON SampleEventIdPartitionScheme(event_id);
GO

CREATE CLUSTERED INDEX CX_SampleEvents_EventId
ON dbo.SampleEvents(event_id)
ON SampleEventIdPartitionScheme(event_id);
GO

-- Inspect boundary values and row distribution.
SELECT
    pf.name AS partition_function,
    prv.boundary_id,
    prv.value AS boundary_value
FROM sys.partition_functions AS pf
LEFT JOIN sys.partition_range_values AS prv
    ON prv.function_id = pf.function_id
WHERE pf.name = N'SampleEventIdPartitionFunction'
ORDER BY prv.boundary_id;

SELECT
    $PARTITION.SampleEventIdPartitionFunction(event_id) AS partition_number,
    COUNT_BIG(*) AS row_count
FROM dbo.SampleEvents
GROUP BY $PARTITION.SampleEventIdPartitionFunction(event_id)
ORDER BY partition_number;

-- Cleanup for a disposable test database:
-- DROP TABLE dbo.SampleEvents;
-- DROP PARTITION SCHEME SampleEventIdPartitionScheme;
-- DROP PARTITION FUNCTION SampleEventIdPartitionFunction;