sql-server - 存储过程中的分页、排序和过滤 (SQL Server)

Question

我正在研究编写存储过程以返回数据“页面”的不同方法。这是与 ASP ObjectDataSource 一起使用的，但它可以被认为是一个更普遍的问题。

需求是根据通常的分页参数返回数据的子集； startPageIndex 和 maximumRows，还有一个 sortBy 参数来允许对数据进行排序。另外还有一些参数传入，用于根据各种条件过滤数据。

一种常见的方法似乎是这样的:

[方法一]

;WITH stuff AS (
    SELECT 
        CASE 
            WHEN @SortBy = 'Name' THEN ROW_NUMBER() OVER (ORDER BY Name)
            WHEN @SortBy = 'Name DESC' THEN ROW_NUMBER() OVER (ORDER BY Name DESC)
            WHEN @SortBy = ... 
            ELSE ROW_NUMBER() OVER (ORDER BY whatever)
        END AS Row,
        ., 
        ., 
        .,
    FROM Table1
    INNER JOIN Table2 ...
    LEFT JOIN Table3 ...
    WHERE ... (lots of things to check)
    ) 
SELECT *
FROM stuff 
WHERE (Row > @startRowIndex)
AND   (Row <= @startRowIndex + @maximumRows OR @maximumRows <= 0)
ORDER BY Row

这样做的一个问题是它没有给出总数，通常我们需要另一个存储过程。第二个存储过程必须复制参数列表和复杂的 WHERE 子句。不太好。

一种解决方案是向最终选择列表添加一个额外的列，(SELECT COUNT(*) FROM stuff) AS TotalRows。这给了我们总数，但对结果集中的每一行重复它，这并不理想。

[方法2]
这里给出了一个有趣的替代方案( https://web.archive.org/web/20211020111700/https://www.4guysfromrolla.com/articles/032206-1.aspx )，使用动态 SQL。他认为性能更好是因为第一个解决方案中的 CASE 语句拖累了事情。很公平，这个解决方案可以轻松获取 TotalRows 并将其放入输出参数中。但我讨厌编写动态 SQL 代码。所有“一点 SQL”+ STR(@parm1) +“更多 SQL”gubbins。

[方法3]
我能找到的获得我想要的东西、无需重复必须同步的代码并保持内容合理可读的唯一方法是回到使用表变量的“旧方法”:

DECLARE @stuff TABLE (Row INT, ...)

INSERT INTO @stuff
SELECT 
    CASE 
        WHEN @SortBy = 'Name' THEN ROW_NUMBER() OVER (ORDER BY Name)
        WHEN @SortBy = 'Name DESC' THEN ROW_NUMBER() OVER (ORDER BY Name DESC)
        WHEN @SortBy = ... 
        ELSE ROW_NUMBER() OVER (ORDER BY whatever)
    END AS Row,
    ., 
    ., 
    .,
FROM Table1
INNER JOIN Table2 ...
LEFT JOIN Table3 ...
WHERE ... (lots of things to check)

SELECT *
FROM stuff 
WHERE (Row > @startRowIndex)
AND   (Row <= @startRowIndex + @maximumRows OR @maximumRows <= 0)
ORDER BY Row

(或使用表变量上的 IDENTITY 列的类似方法)。在这里，我可以在表变量上添加一个 SELECT COUNT 来获取 TotalRows 并将其放入输出参数中。

我做了一些测试，并且使用相当简单的查询版本(没有 sortBy 和过滤器)，方法 1 似乎名列前茅(几乎是其他 2 种方法的两倍)。然后我决定测试一下我可能需要的复杂性，并且我需要 SQL 位于存储过程中。这样我得到的方法 1 花费的时间几乎是其他 2 种方法的两倍。这看起来很奇怪。

有什么充分的理由让我不应该放弃 CTE 并坚持使用方法 3？

<小时/>

更新 - 2012 年 3 月 15 日

我尝试采用方法 1 将页面从 CTE 转储到临时表中，以便我可以提取 TotalRows，然后仅选择结果集的相关列。这似乎显着增加了时间(超出了我的预期)。我应该补充一点，我在装有 SQL Server Express 2008(我拥有的所有可用工具)的笔记本电脑上运行此程序，但比较应该是有效的。

我又看了一遍动态SQL的方法。事实证明我并没有真正正确地做到这一点(只是将字符串连接在一起)。我按照 sp_executesql 的文档进行设置(带有参数描述字符串和参数列表)，并且它的可读性更强。而且这种方法在我的环境中运行得最快。为什么这仍然让我困惑，但我想霍根的评论中暗示了答案。

Answer 1

我很可能会将@SortBy参数分成两个，@SortColumn和@SortDirection，并像这样使用它们:

…
ROW_NUMBER() OVER (
  ORDER BY CASE @SortColumn
    WHEN 'Name'      THEN Name
    WHEN 'OtherName' THEN OtherName
    …
  END *
  CASE @SortDirection
    WHEN 'DESC' THEN -1
    ELSE 1
  END
) AS Row
…

这就是定义 TotalRows 列的方式(在主选择中):

…
COUNT(*) OVER () AS TotalRows
…