SQL Server中不使用COUNT函数获取表行数的异常问题求助

Let's tackle your problem head-on—you're trying to get table row counts without COUNT(), but your current sys.partitions query is giving inconsistent results, and sys.dm_db_partition_stats isn't supported in your environment. Here's how to fix this:

Why Your Current sys.partitions Query Is Wrong #

The rows column in sys.partitions is an approximate statistic used by SQL Server's query optimizer, not a real-time accurate count. Your original query also includes non-clustered indexes (index IDs >1) which count index entries, not actual table rows—this is why you're seeing inflated or inconsistent numbers compared to COUNT(*).

Corrected sys.partitions Query #

Filter for only the heap (index ID 0) or clustered index (index ID 1) partitions, since these represent the actual table data. We'll also sum rows across partitions if your tables are partitioned:

SELECT 
    CONCAT(schemas.name, '.', tables.name) AS tableName,
    SUM(partitions.rows) AS tableRowCount
FROM 
    sys.partitions
JOIN 
    sys.tables ON tables.object_id = partitions.object_id
JOIN 
    sys.schemas ON tables.schema_id = schemas.schema_id
WHERE 
    partitions.index_id IN (0, 1) -- Only include heap or clustered index data
GROUP BY 
    schemas.name, tables.name;

Improve Accuracy of Approximate Counts #

To make the rows values as close as possible to the real count, update the statistics for your tables:

UPDATE STATISTICS [YourSchema.YourTableName];

Run this periodically (especially after large data modifications) to refresh the optimizer's stats. Note: This still gives an approximation, not a 100% match with COUNT(*).

Fixing the sys.dm_db_partition_stats Error #

If that DMV isn't supported in your SQL Server/Azure SQL Database version, you have a couple alternatives:

Use the Legacy sys.sysindexes View #

This is a compatibility view (deprecated but still functional in most versions) that provides similar approximate row counts:

SELECT 
    CONCAT(schemas.name, '.', tables.name) AS tableName,
    sysindexes.rows AS tableRowCount
FROM 
    sys.sysindexes
JOIN 
    sys.tables ON tables.object_id = sysindexes.id
JOIN 
    sys.schemas ON tables.schema_id = schemas.schema_id
WHERE 
    sysindexes.indid IN (0, 1);

Just keep in mind: Microsoft may remove this view in future releases, so stick with sys.partitions when possible.

Critical Note: Approximate vs. Exact Counts #

If your use case requires 100% accurate row counts, there's no way around using COUNT(*) (or COUNT(1)). All system views rely on cached statistics, which are never perfectly real-time. The methods above work for quick estimates, but only a full table scan via COUNT(*) will give you the exact number.

内容的提问来源于stack exchange，提问作者Dipanjan Mallick