sql - Count(*) 不同于 sys.partitions 中的行

Question

我正在使用以下查询来获取有关数据库中所有表的信息:

SELECT 
    t.NAME AS TableName,
    i.name as indexName,
    sum(p.rows) as RowCounts,
    sum(a.total_pages) as TotalPages, 
    sum(a.used_pages) as UsedPages, 
    sum(a.data_pages) as DataPages,
    (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB, 
    (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB, 
    (sum(a.data_pages) * 8) / 1024 as DataSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    t.NAME NOT LIKE 'dt%' AND
    i.OBJECT_ID > 255 AND   
    i.index_id <= 1
GROUP BY 
    t.NAME, i.object_id, i.index_id, i.name 
ORDER BY 
    object_name(i.object_id)

问题是对于某些表，它报告的行数与我报告的行数不同:

select count(*) FROM someTable

这是为什么？

编辑:

第一个查询返回更高的计数:

First: 1 240 464
Second:  413 496

Answer 1

问题是每个分区有多个 allocation_unit，所以同一个分区可以出现不止一次，因此 sum(p.rows) 最终会不止一次地计算同一个分区，所以你得到的是正确的行数。

我是这样解决问题的:(请注意，我的查询与您的不同，我的列略有不同，使用的是 Kb 而不是 Mb，但思路是一样的)

    SELECT 
        s.Name + '.' + t.name AS table_name,
        (select sum(p2.rows)
            from sys.indexes i2 inner join sys.partitions p2 ON i2.object_id = p2.OBJECT_ID AND i2.index_id = p2.index_id
            where i2.object_id = t.object_id and i2.object_id > 255 and (i2.index_id = 0 or i2.index_id = 1)
        ) as total_rows,
        SUM(CASE WHEN (i.index_id=0) OR (i.index_id=1) THEN a.total_pages * 8 ELSE 0 END) AS data_size_kb,
        SUM(CASE WHEN (i.index_id=0) OR (i.index_id=1) THEN a.used_pages * 8 ELSE 0 END) AS data_used_kb,
        SUM(CASE WHEN (i.index_id=0) OR (i.index_id=1) THEN 0 ELSE a.total_pages * 8 END) AS index_size_kb,
        SUM(CASE WHEN (i.index_id=0) OR (i.index_id=1) THEN 0 ELSE a.used_pages * 8 END) AS index_used_kb,
        SUM(a.total_pages) * 8 AS total_size_kb, 
        SUM(a.used_pages) * 8 AS total_used_kb,
        SUM(a.used_pages) * 100 / CASE WHEN SUM(a.total_pages) = 0 THEN 1 ELSE SUM(a.total_pages) END AS percent_full
    FROM 
        sys.tables t
    INNER JOIN 
        sys.schemas s ON s.schema_id = t.schema_id
    INNER JOIN      
        sys.indexes i ON t.OBJECT_ID = i.object_id
    INNER JOIN 
        sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
    INNER JOIN 
        sys.allocation_units a ON p.partition_id = a.container_id
    WHERE 
        t.is_ms_shipped = 0 AND i.OBJECT_ID > 255 
    GROUP BY 
        t.object_id, t.Name, s.Name
    ORDER BY SUM(a.total_pages) DESC