在許多開發情境下經常會試圖取回整個資料表的筆數，像我們就有個統計網站累積使用人次功能，開發人員寫的時候就是用 SELECT COUNT(*) FROM TableName 的方式來取得目前總共有多少人次進入網站，但用這種方式執行效率並不高，每次執行都會產生一次 Index Scan，也就是在 SQL Server 資料庫裡所有主索引鍵的資料都會被掃瞄過一次才能統計出資料總筆數，當資料越多的時候，效能就會越差，在這裡我提供另一種 hack 的方法可以更有效率的取得整個表格的總筆數，但此法僅適用於 SQL Server 喔！
在 SQL Server 裡，可以透過查詢 sys.partitions 系統資料表來取得資料表的總筆數，如下語法：
SELECT
ISNULL(
( select sum (spart.rows)
from sys.partitions spart
where spart.object_id=object_id('SalesLT.Product') and spart.index_id < 2),
0
) AS [RowCount]
注意：執行時，如上 SalesLT.Product 要換成要查詢的「表格名稱」
而套用到 Entity Framework 的方法很多，可以透過定義 Stored Procedure 或 Function 的方式，以下是可參考的範例 T-SQL 語法：
預存程序 (Stored Procedure)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE GetRowCount
@TableName nvarchar(255)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
SET NOCOUNT ON;
-- Declare the return variable here
DECLARE @Result INT
-- Add the T-SQL statements to compute the return value here
SET @Result =
ISNULL(
( select sum (spart.rows)
from sys.partitions spart
where spart.object_id=object_id(@TableName) and spart.index_id < 2),
0
)
-- Return the result of the function
RETURN @Result
END
GO
函數 ( Function )
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION GetRowCount
(
@TableName nvarchar(255)
)
RETURNS int
AS
BEGIN
-- Declare the return variable here
DECLARE @Result INT
-- Add the T-SQL statements to compute the return value here
SET @Result =
ISNULL(
( select sum (spart.rows)
from sys.partitions spart
where spart.object_id=object_id(@TableName) and spart.index_id < 2),
0
)
-- Return the result of the function
RETURN @Result
END
GO
今天我來講一個更簡單的方式，那就是直接透過 Entity Framework 的 ExecuteStoreQuery 方法查詢，直接取得總數的結果，無須在SQL Server 資料庫裡額外定義預存程序或函數，也不用先修改 EDMX 定義。
這樣寫雖然失去了 Entity Framework 設計的初衷 (ORM)，但是卻簡化了開發的複雜度，省去一堆設定的麻煩，況且這樣的功能說實在蠻少見的，就只有幾個地方會用到這種 SELECT COUNT(*) FROM TableName 的應用。
以下就是執行時的程式碼，執行時只要把 SalesLT.Product 換成要查詢的資料表名稱即可：
var num = db.ExecuteStoreQuery<Int64>(@"SELECT ISNULL((select sum (spart.rows)
from sys.partitions spart where spart.object_id=object_id({0})
and spart.index_id < 2),0)", "SalesLT.Product").First();
就這樣一條陳述式就搞定取得總筆數的任務又擁有極佳的查詢效能，是不是很簡單呢！ ^_^
注意：唯一特別要提醒的就是取得總筆數回來的型別為 Int64 才對喔。
話說我是怎樣找出這段 T-SQL 的呢？其實是從 Management Studio 挖來的，如下圖示，當取得該資料庫所有表格的摘要資訊時，同時用 SQL Server Profiler 去收資料庫正在執行的 T-SQL 就知道到底他在背景對資料庫下了什麼查詢，如此一來就可以知道很多 Management Studio 不為人知的秘密喔，嘿嘿。
補充說明
感謝 SQL Hero 五虎將之首 RiCo 提醒說：透過 sysindexes、partitions和sp_spaceused 取得資料筆數數值可能會因為更新統計值時間誤差而不準確， 最保險作法請先執行 DBCC UPDATEUSAGE ('db_name', 'table_name') WITH COUNT_ROWS，然後在取得筆數的資訊，不過但整體執行時間將拉長。
如果有人對於「筆數」的精準度很要求的話，建議還是用 SELECT COUNT(*) FROM TableName 的方式來取得總筆數比較安全，如果以「統計網站累積使用人次功能」來說，數字差一點點其實沒關係的。
相關連結