Counting Rows Efficiently with sys.partitions

The Problem with COUNT(*)

On a small table, SELECT COUNT(*) FROM dbo.Orders is free. On a table with hundreds of millions of rows, it forces a full scan of the smallest available index (or the clustered index/heap if no nonclustered index covers the query): every single row has to be visited to be counted. On a busy production server, that scan competes for I/O and buffer pool space with real workload, and can take minutes on genuinely large tables.

The Metadata Shortcut

SQL Server already tracks row counts per partition, per index, internally, for every table, updated as part of normal DML, independent of statistics. You can read it directly from sys.partitions without touching a single data page:

SELECT
    SCHEMA_NAME(t.schema_id) AS SchemaName,
    t.name                   AS TableName,
    SUM(p.rows)               AS RowCount
FROM sys.tables t
INNER JOIN sys.partitions p
    ON t.object_id = p.object_id
WHERE p.index_id IN (0, 1)   -- 0 = heap, 1 = clustered index
GROUP BY t.schema_id, t.name
ORDER BY RowCount DESC;

This query returns instantly regardless of table size, because it reads a handful of rows from a system catalog view rather than scanning the table itself. The key filter is index_id IN (0, 1): a table can have many nonclustered indexes, each with its own partition row count, and you only want the base table's count once (heap = 0, clustered = 1; never both exist on the same table).

Why This Works

sys.partitions.rows holds the row count for that specific partition of that specific index, maintained transactionally as rows are inserted, updated, or deleted. Unlike statistics (which are sampled and can go stale), this number is exact and always current; it's not an estimate.

For a non-partitioned table, there's exactly one row in sys.partitions per index (partition_number = 1). For a partitioned table, there's one row per partition per index: summing them gives you the table total, or you can group by partition_number to get per-partition counts.

Per-Partition Breakdown

This is where sys.partitions earns its keep beyond just replacing COUNT(*); you get row distribution across partitions for free, which is invaluable when checking whether a partitioned table's data is skewed:

SELECT
    p.partition_number,
    p.rows,
    prv.value AS BoundaryValue
FROM sys.partitions p
INNER JOIN sys.indexes i
    ON p.object_id = i.object_id AND p.index_id = i.index_id
LEFT JOIN sys.partition_schemes ps
    ON i.data_space_id = ps.data_space_id
LEFT JOIN sys.partition_functions pf
    ON ps.function_id = pf.function_id
LEFT JOIN sys.partition_range_values prv
    ON pf.function_id = prv.function_id AND p.partition_number = prv.boundary_id
WHERE p.object_id = OBJECT_ID('dbo.FactSales')
  AND p.index_id IN (0, 1)
ORDER BY p.partition_number;

Getting this from COUNT(*) ... GROUP BY $PARTITION.MyPartitionFunction(...) would require a full table scan; reading it from sys.partitions costs nothing.

Where It Breaks Down

Scenario	sys.partitions behavior
Filtered query (`WHERE Status = 'Open'`)	Not applicable: `sys.partitions` only gives total row counts, no filtering
Query inside an open transaction with uncommitted inserts	Reflects the count as SQL Server sees it right now, including your own session's uncommitted changes; other sessions' uncommitted rows are also already counted internally regardless of isolation level, since this is metadata, not a locked read
In-memory OLTP (memory-optimized) tables	`sys.partitions` does not track row counts the same way; use `sys.dm_db_xtp_object_stats` instead
Immediately after a large bulk load without proper commit	Row counts update as part of the DML operation, not as a separate maintenance step, so this is generally safe, but always verify counts match expectations after any bulk operation

The biggest limitation: this technique only gives you unfiltered, whole-table (or whole-partition) counts. If you need COUNT(*) WHERE SomeColumn = 'X', you're back to a real scan (or, in read-committed-snapshot-heavy systems, an index seek if a suitable index exists): metadata can't answer a predicate for you.

A Reusable Procedure

Wrap the base query into a stored procedure once, and reach for it any time you need a quick sanity check on table sizes across a database, useful for capacity checks, migration validation, or spotting a table that's grown unexpectedly:

CREATE OR ALTER PROCEDURE dbo.sp_GetTableRowCounts
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        SCHEMA_NAME(t.schema_id) AS SchemaName,
        t.name                   AS TableName,
        SUM(p.rows)               AS RowCount,
        CAST(SUM(a.total_pages) * 8.0 / 1024 AS DECIMAL(12,2)) AS SizeMB
    FROM sys.tables t
    INNER JOIN sys.partitions p
        ON t.object_id = p.object_id AND p.index_id IN (0, 1)
    INNER JOIN sys.allocation_units a
        ON p.partition_id = a.container_id
    GROUP BY t.schema_id, t.name
    ORDER BY RowCount DESC;
END;

The Bottom Line

For an exact, filter-free row count on a large table, sys.partitions is essentially free and always accurate: no scan, no wait. Use it for dashboards, monitoring jobs, and capacity checks where you need "how big is this table" rather than "how many rows match this condition." Keep COUNT(*) WHERE ... for anything that actually needs a predicate.

← Back to Blog