SQL分页返回分页之前记录的总数

I use a stored proc to paginate server side on a big recordset The stored proc has a quite complex where clause.

我使用一个存储的proc对一个大型记录集的服务器端进行分页，存储的proc有一个非常复杂的where子句。

I also need the total amount of records before pagination to be able to visualize that number on the UI.

我还需要分页之前的记录总数才能在UI上显示该数字。

What is the best way of doing it?

最好的方法是什么?

at the moment I’m doing

现在我正在做

ALTER PROCEDURE [Schema].[ReturnRecords]
@PageSize int = 20,
@StartIndex int = 0
AS
SELECT * FROM
(
    SELECT *, TOP(COUNT(e._ROWID) OVER ())
    FROM TABLE_NAME
    WHERE Column=1 AND Column1=2 AND Column2=2 AND Column3=2 AND Column4=2
) AS X
WHERE X.RowNum BETWEEN @StartIndex AND (@StartIndex + @PageSize)

so my last column of the record contains the total amount of records before pagination.

因此，记录的最后一列包含分页之前记录的总数。

The alternative is to create a temporary table with the inner query, then count the records and return the temporary table but it does not look like a good way of doing it...

另一种方法是使用内部查询创建一个临时表，然后计算记录并返回临时表，但这种方法看起来不太好……

I would love to return the total number of records as an output parameter of the stored proc

我希望将记录的总数作为存储的proc的输出参数返回

any idea?

任何想法?

1 个解决方案

#1

Doing a second query to get the count can often turn out to be a more efficient strategy. See some performance comparisons of various approaches here.

执行第二个查询来获取计数通常是一种更有效的策略。请参阅这里各种方法的性能比较。