从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.

之前，我获得了整个记录集，并能够以编程方式在该页面上建立分页.但是，仅将SQL方法与FETCH NEXT X ROWS一起使用时，我只得到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屋！