SQL Server 使用什麼來對錶進行計數（*）？

在內部，SQL Server 如何確定 a 的結果select(*)？堆和聚集索引之間有區別嗎？是否能夠使用聚集索引做一些聰明的事情而不必載入整個表？

通過對基於磁碟的表的簡單SELECT COUNT(*) FROM dbo.YourTable;查詢，SQL 伺服器通過掃描索引的堆或葉節點來計算行數。基於成本的 SQL Server 優化器會選擇可用的最窄的優化器，以最大限度地減少掃描的頁數。

此範例顯示了索引掃描的行為。

CREATE TABLE dbo.YourTable(
     Column1 int NOT NULL
   );
--load 10K rows
WITH 
    t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
   ,t1k AS (SELECT 0 AS n FROM t10 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c)
   ,t1g AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS num FROM t1k AS a CROSS JOIN t1k AS b CROSS JOIN t1k AS c)
INSERT INTO dbo.YourTable WITH (TABLOCKX) (Column1) 
SELECT num
FROM t1g
WHERE num <= 10000;
GO
CREATE INDEX ncidx_YourTable_IntColumn ON dbo.YourTable(Column1);
GO
--index scan
SELECT COUNT(*) FROM dbo.YourTable;
GO
--show heap and index size
SELECT
   i.name              AS IndexName,
   SUM(page_count * 8) AS IndexSizeKB
FROM sys.dm_db_index_physical_stats(
   db_id(), object_id('dbo.YourTable'), NULL, NULL, 'DETAILED') AS s
JOIN sys.indexes AS i
ON s.object_id = i.object_id AND s.index_id = i.index_id
GROUP BY i.name
ORDER BY i.name;

索引/堆大小：

+---------------------------+-------------+
|         IndexName         | IndexSizeKB |
+---------------------------+-------------+
| NULL                      |         272 |
| ncidx_YourTable_IntColumn |         192 |
+---------------------------+-------------+

如果我們對索引進行分段，它會變得比堆大，因此掃描的是堆而不是索引：

--fragment index and update stats
UPDATE dbo.YourTable SET Column1 = 2;
UPDATE STATISTICS dbo.YourTable;
DBCC FREEPROCCACHE;
--heap scan
SELECT COUNT(*) FROM dbo.YourTable;
GO

帶有碎片的堆和索引大小：

+---------------------------+-------------+
|         IndexName         | IndexSizeKB |
+---------------------------+-------------+
| NULL                      |         272 |
| ncidx_YourTable_IntColumn |         648 |
+---------------------------+-------------+

重建索引後，再次掃描索引，因為它更窄：

--rebuild index
ALTER INDEX ncidx_YourTable_IntColumn ON dbo.YourTable REBUILD;
DBCC FREEPROCCACHE;
--index scan
SELECT COUNT(*) FROM dbo.YourTable;
GO

重建後的堆和索引大小：

SELECT
   i.name              AS IndexName,
   SUM(page_count * 8) AS IndexSizeKB
FROM sys.dm_db_index_physical_stats(
   db_id(), object_id('dbo.YourTable'), NULL, NULL, 'DETAILED') AS s
JOIN sys.indexes AS i
ON s.object_id = i.object_id AND s.index_id = i.index_id
GROUP BY i.name
ORDER BY i.name;
GO

+---------------------------+-------------+
|         IndexName         | IndexSizeKB |
+---------------------------+-------------+
| NULL                      |         272 |
| ncidx_YourTable_IntColumn |         192 |
+---------------------------+-------------+

引用自：https://dba.stackexchange.com/questions/243746