SQL -使用CTE并有效地选择特定row_number()中的行

Question

在这个场景中，CTE (我知道.，微观优化)是必要的选择顶部。

DECLARE @pageSize INT, @currentPage INT, @top INT
SET @pageSize = 10
SET @currentPage = 150
SET @top = @pageSize * @currentPage + @pageSize

WITH t AS
(
    SELECT TOP(***@top***) ID, name
    ROW_NUMBER() OVER (ORDER BY ID) AS _row,
    FROM dbo.User
)
SELECT TOP(@pageSize) *
FROM t
WHERE t._row > (@top-@pageSize)
ORDER BY t.ID

上面的代码以特定的顺序从起始编号(@top-@pageSize)返回10行(@pageSize)，其中包含行号列。CTE语句是否知道CTE之外的"SELECT TOP“和CTE之外的WHER-子句也将出现，因此CTE不会按特定顺序返回超过需要的行数？

基本上，只讨论ROW_NUMBER函数，它不为未返回的行计数行号(如果我有数百万行.)，而且如果我要在CTE中选择前100行，那么对于所选表中的所有百万行，还会计算row_number吗？

我尝试过在CTE-语句中使用和不使用"SELECT TOP(@top)“，在循环中运行10.000次，没有看到时间使用上的任何差别。不过，我现在的桌子上只有38.000排。

编辑:所以结果：

    WITH t AS
(
    **DO A TOP() WITH AN ORDER BY IN THE CTE**
    SELECT TOP(@top) ID, name 
    ROW_NUMBER() OVER (ORDER BY ID) AS _row,
    FROM dbo.User
    ORDER BY ID
)
SELECT TOP(@pageSize) * 
**SELECTING TOP N FROM THE CTE, WHERE ROW-NUMBER IS ... DUE TO THE CTE IS IN ORDER ALREADY**
FROM t
WHERE t._row > (@top-@pageSize)

如果我命令它们“向后”，选择CTE的“底部@pageSize”，这可能会更有效，这将省略where-子句.但如果它真的更快的话.

Answer 1

不鼓励使用没有top的order by。无法保证您将得到所需的行，因此不应该包含top。或者，您应该包括一个order by id，如果这是您想要的排序。

top的用户不影响row_number()计算，因为该计算将在应用top之前完成。您可以想象在那里有另一个窗口函数，例如sum() over ()，以理解top通常不能在row_number()之前应用，找到安全的环境将是一项艰巨的工作。

Answer 2

如果在ID上有一个支持索引，则不必读取和枚举整个表。Server必须将表读取到并包括所需的页。因此，例如，如果您想要第1页(第11行到第20行)，则查询将只获取20行。这是真的，即使你没有使用顶部的CTE。

要用某些数据测试的表：

create table dbo.[User]
(
  ID int identity primary key,
  Name nvarchar(128)
)

go

insert into dbo.[User](Name)
select top(1000) Name
from sys.all_objects

没有冗余顶部表达式的查询。

DECLARE @pageSize INT, @currentPage INT, @top INT;
SET @pageSize = 10;
SET @currentPage = 1;
SET @top = @pageSize * @currentPage + @pageSize;
with C as
(
  select U.ID,
         U.Name,
         row_number() over(order by U.ID) as rn
  from dbo.[User] as U
)
select C.ID,
       C.Name
from C
where C.rn > @pageSize * @currentPage and 
      C.rn <= @pageSize * (@currentPage + 1);

这将为您提供如下查询计划：

​

​

每个运算符的数字是实际获取的行数。聚集索引扫描读取由ID排序的20行。段和序列项目枚举行。Top是确保不超过20行的操作符。过滤器移除第1行至第10行，并让第11行至第20行通过。

如果我们尝试获得第5页(@currentPage = 5获取第51行到第60行)，则计划如下：

​

​

Top运算符确保只从聚集索引中读取60行，筛选器筛选出前50行以返回最后10行。

使用最后一个带有额外顶部表达式的查询将不会增加任何有价值的内容。只有一个多余的顶级操作员。

​

​

要理解查询计划中正在发生的事情，关键是要知道执行是从左到右完成的，要求每次只执行一行。这就是当返回足够多行时，top运算符可以停止聚集索引扫描的方式。