从 OFFSET/FETCH NEXT 获取总行数 [英] Getting total row count from OFFSET / FETCH NEXT

问题描述

所以，我有一个函数可以返回许多我想在我的网站上实现分页的记录.有人建议我使用 SQL Server 2012 中的 Offset/Fetch Next 来完成此操作.在我们的网站上，我们有一个区域列出了记录总数以及您当时所在的页面.

So, I've got a function that returns a number of records that I want to implement paging for on my website. It was suggested to me that I use the Offset/Fetch Next in SQL Server 2012 to accomplish this. On our website, we have an area that lists total number of records and what page you're on at the time.

之前，我获得了整个记录集，并能够以编程方式在其上构建分页.但是使用带有 FETCH NEXT X ROWS ONLY 的 SQL 方式，我只得到 X 行，所以我不知道我的总记录集是什么以及如何计算我的最小和最大页面.我能告诉这样做的唯一方法是调用该函数两次并在第一个上计算行数，然后使用 FETCH NEXT 运行第二个.有没有更好的方法不会让我运行查询两次?我正在努力提高性能，而不是降低性能.

Before, I was getting the entire record set and was able to build the paging on that programatically. But using the SQL way with FETCH NEXT X ROWS ONLY, I am only given back X rows, so I don't know what my total record set is and how to calculate my min and max pages. The only way I can tell of doing this is calling the function twice and doing a count of rows on the first, then running the second with FETCH NEXT. Is there a better way that won't have me running the query twice? I am trying to speed up performance, not slow it down.

推荐答案

您可以使用 COUNT(*) OVER() ... 这里是一个使用 sys.all_objects<的快速示例/代码>:

You can use COUNT(*) OVER() ... here is a quick example using sys.all_objects:

DECLARE 
  @PageSize INT = 10, 
  @PageNum  INT = 1;

SELECT 
  name, object_id, 
  overall_count = COUNT(*) OVER()
FROM sys.all_objects
ORDER BY name
  OFFSET (@PageNum-1)*@PageSize ROWS
  FETCH NEXT @PageSize ROWS ONLY;

但是，这应该是为小数据集保留的；在较大的集上，性能可能很糟糕.请参阅这篇 Paul White 文章以了解更好的替代方案，包括维护索引视图(仅当结果未过滤或您事先知道 WHERE 子句时才有效)并使用 ROW_NUMBER() 技巧.

However, this should be reserved for small data sets; on larger sets, the performance can be abysmal. See this Paul White article for better alternatives, including maintaining indexed views (which only works if the result is unfiltered or you know WHERE clauses in advance) and using ROW_NUMBER() tricks.

这篇关于从 OFFSET/FETCH NEXT 获取总行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！