Partition Function Fanout: Count Partitions in SQL Server

Count partitions in SQL Server by reading the fanout of a partition function. Fanout is the number of partitions that the function defines. It tells you at a glance how far a function has grown.

What Fanout Means

A partition function is a list of boundary values. The boundaries cut a range of values into partitions. Five boundaries make six partitions, so the partition function fanout is always the number of boundaries plus one. Every table and index that uses the function through a partition scheme gets that many partitions.

Fanout is the number of partitions the function defines now, not the number ever created. A MERGE lowers it. The column modify_date is the last time someone altered the function, with a SPLIT or a MERGE. It is not the date of the newest partition.

Build Two Functions

The demo creates a database named PartitionFnDemo with two partition functions. The first cuts dates by month and has five boundaries. The second cuts a number into four ranges and has three. Only the first has a table. All partitions sit on the primary filegroup, which keeps the demo simple. A scheme has no IF EXISTS form for DROP, so the setup checks the catalog first.

IF DB_ID(N'PartitionFnDemo') IS NULL CREATE DATABASE PartitionFnDemo;
GO
USE PartitionFnDemo;
GO
DROP TABLE IF EXISTS dbo.MonthlySales;
IF EXISTS (SELECT 1 FROM sys.partition_schemes WHERE name = N'psMonths') DROP PARTITION SCHEME psMonths;
IF EXISTS (SELECT 1 FROM sys.partition_functions WHERE name = N'pfMonths') DROP PARTITION FUNCTION pfMonths;
IF EXISTS (SELECT 1 FROM sys.partition_schemes WHERE name = N'psRegions') DROP PARTITION SCHEME psRegions;
IF EXISTS (SELECT 1 FROM sys.partition_functions WHERE name = N'pfRegions') DROP PARTITION FUNCTION pfRegions;
GO
CREATE PARTITION FUNCTION pfMonths (date) AS RANGE RIGHT FOR VALUES ('2026-02-01', '2026-03-01', '2026-04-01', '2026-05-01', '2026-06-01');
CREATE PARTITION SCHEME psMonths AS PARTITION pfMonths ALL TO ([PRIMARY]);
CREATE PARTITION FUNCTION pfRegions (int) AS RANGE LEFT FOR VALUES (10, 20, 30);
CREATE PARTITION SCHEME psRegions AS PARTITION pfRegions ALL TO ([PRIMARY]);
CREATE TABLE dbo.MonthlySales (SaleDate date NOT NULL, Amount decimal(9,2) NOT NULL) ON psMonths (SaleDate);

Count the Partitions

To count partitions in SQL Server, the query reads fanout and the boundary side. RANGE RIGHT means the boundary value belongs to the partition on its right. It also counts the schemes that use each function. It shows whether anyone altered the function since it was created.

SELECT pf.name AS FunctionName, pf.fanout AS Partitions,
       pf.boundary_value_on_right AS BoundaryOnRight,
       CASE WHEN pf.modify_date > pf.create_date THEN N'Yes' ELSE N'No' END AS AlteredSinceCreate,
       (SELECT COUNT(*) FROM sys.partition_schemes AS ps WHERE ps.function_id = pf.function_id) AS Schemes
FROM sys.partition_functions AS pf
ORDER BY pf.name;

Six partitions for five boundaries, four for three. The side of the boundary is 1 for the month function and 0 for the number function.

Watch Fanout Change

Now add a month. A SPLIT RANGE needs a filegroup marked as the next one to use. Then the fanout grows by one. A MERGE RANGE removes a boundary and lowers it again. The MERGE below removes the oldest boundary.

ALTER PARTITION SCHEME psMonths NEXT USED [PRIMARY];

ALTER PARTITION FUNCTION pfMonths() SPLIT RANGE ('2026-07-01');

SELECT pf.name AS FunctionName, pf.fanout AS Partitions,
       CASE WHEN pf.modify_date > pf.create_date THEN N'Yes' ELSE N'No' END AS AlteredSinceCreate
FROM sys.partition_functions AS pf
ORDER BY pf.name;

ALTER PARTITION FUNCTION pfMonths() MERGE RANGE ('2026-02-01');

SELECT pf.name AS FunctionName, pf.fanout AS Partitions
FROM sys.partition_functions AS pf
ORDER BY pf.name;

The month function went to seven partitions and showed Yes in the altered column. After the merge it was back at six, and it kept the Yes. The number function never changed. A function whose fanout climbs every week is a function that a job splits without a matching merge. That is the one worth a look. Partition functions belong to a database, so run the query in each database that uses partitioning.

The merge removed the boundary 2026-02-01 and joined the two partitions around it. The boundary list shows what is left. The value column holds the boundaries as a general type, so the query converts it to a date.

SELECT v.boundary_id AS BoundaryID, CONVERT(date, v.value) AS Boundary
FROM sys.partition_range_values AS v
JOIN sys.partition_functions AS f ON f.function_id = v.function_id
WHERE f.name = N'pfMonths'
ORDER BY v.boundary_id;

Five boundaries and six partitions, the same rule again.

Find Functions No Table Uses

A function can outlive its tables. The query below joins the functions to their schemes and to the indexes that sit on those schemes. A NULL scheme name means no scheme uses the function. A NULL table name means no table or index uses the scheme.

SELECT pf.name AS FunctionName, ps.name AS SchemeName, OBJECT_NAME(i.object_id) AS TableName
FROM sys.partition_functions AS pf
LEFT JOIN sys.partition_schemes AS ps ON ps.function_id = pf.function_id
LEFT JOIN (SELECT DISTINCT object_id, data_space_id FROM sys.indexes) AS i ON i.data_space_id = ps.data_space_id
ORDER BY pf.name, TableName;

The number function has a scheme but no table. A real server has leftovers like that after a migration. To remove one, drop the scheme first and the function second, as the demo setup does. SQL Server refuses to drop a function that a scheme still uses. A table can have up to 15,000 partitions. A function with a huge fanout can reach that limit. For the rows in each partition, read Get Partition Info in SQL Server: Rows and Boundaries.

Does Partitioning Make Queries Faster?

You could argue that partitioning speeds up a huge table. A filter on the partition column reads one partition and skips the rest. That is true when the filter matches the column and the table has billions of rows. A good index can do the same job with less to maintain.

In my experience partitioning helps data management more than speed. It makes it cheap to switch old data out and to load new data in. Watch the fanout so that the management stays cheap.

What to Remember

Count partitions in SQL Server with fanout, which is boundaries plus one. Read it from sys.partition_functions, and use modify_date to see when someone last altered the function. Compare the number with what you expect, and list the functions that no table uses.

When you finish the demo, remove the database.

USE master;
GO
IF DB_ID(N'PartitionFnDemo') IS NOT NULL
BEGIN
    ALTER DATABASE PartitionFnDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE PartitionFnDemo;
END;

A partition function is not a table, it is a ruler that many tables share.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.