SQL Server 2008中使用Top和Count的最佳方法是什么？

Question

我有一个2008 C#应用程序（后端SQL Server 2008），有时查询会返回大量结果。因此，我研究了一些分页算法，并使用了使用TOP语句的算法。我喜欢的是，如果我有500个总结果，每页只显示20个结果，那么我只需要查询20条记录而不是将所有500条记录存储在内存中。

但我的问题是，在实现分页算法之前，我需要知道此查询中有多少总记录。所以，我所做的就是运行另一个查询，具有相同的参数，只选择ID（而不是整个对象）以尝试使其快速运行。但由于某种原因，我认为运行这两个查询（从而建立2个连接）并不是最佳方法。

因此，我需要所有记录的计数，但只想选择用于TOP的有限数量。这里是否可以使用临时表？我现在有两个不同的存储过程。

感谢任何人可以提供的“最佳实践”建议。

- PTVee

Answer 1

我不知道你用的是什么分页方法，但我通常使用的技术是使用ROW_NUMBER函数，例如：

SELECT  Description, Date
FROM
(
    SELECT  ROW_NUMBER() OVER (ORDER BY Date DESC) AS Row,
        Description, Date 
    FROM Log
) AS LogWithRowNumbers
WHERE  Row >= 1 AND Row <= 10

这种方法是我所知道的最干净的方法，适用于任何子查询，并且只需要一个查询。

当然，如果您希望在页面上显示“总命中数”或类似内容，则还需要另一个查询来获取行计数：

SELECT COUNT(*) FROM Log

然而，您可以在同一批处理中执行这两个语句，以避免对数据库服务器进行额外的“往返”操作。

来源 - 使用 SQL Server 2005 数据库 - ROW_NUMBER 函数进行分页记录

- Justin

---

谢谢回复。我已经在存储过程中使用了ROW_NUMBER()来获取计数。对于这个我使用C#中的ExecuteScalar()。对于使用Top的存储过程，我正在填充一个数据集（.Fill(ds);）。我想我不确定如何从一个存储过程中同时获取两者。但是一旦我有了计数，那么是否会再次不必要地运行计数查询，因为从那里我所需要的只是TOP查询呢？谢谢！ - PTVee

@PTVee 运行一个单独的计数查询并不是真正的“不必要”，因为您无法从主分页查询（无论您选择哪种方法）中获取此信息-您可以从主分页查询中获得的最好信息是当前页面的大小。此外，计数查询通常非常快。 - Justin

好的，Bob在上面回答了我的问题，关于在同一个存储过程中运行两个。非常感谢大家的帮助！ - PTVee

Answer 2

我会尝试这样做：

;WITH CTE AS
(
    SELECT 
        ROW_NUMBER() OVER (ORDER BY Date DESC) AS Row,
        COUNT(*) OVER() AS TotalCount,
        Description, Date 
    FROM Log
)

SELECT *
FROM CTE
WHERE Row BETWEEN 1 AND 10

这将在一个查询中提供分页和计数。您还可以使用CASE语句仅在某些时候进行计数。例如，当用户浏览页面时计数是否会发生变化？

- goalie7960

---

这可能比两个查询效率低，因为您会多次返回计数，并且不确定查询优化器是否会为每一行重新执行计数。 - Bob Vale

这是可能的。在我们的应用程序中，我们使用CASE语句和标志来控制何时执行计数。 - goalie7960

Answer 3

你可以使用对象数据源（ObjectDataSource）来实现这个自定义分页。

- Ovais Khatri

Answer 4

我会使用 ROW_NUMBER() OVER (ORDER BY ...)。更多细节 在这里。

- anivas

Answer 5

如果您在存储过程中执行这两个选择操作，使用Fill方法后，您将在数据集中获得两个数据表。

- Bob Vale

---

啊啊啊！好的，我可以尝试一下！！谢谢！！ - PTVee

也许我的应用程序可以确定我在后续页面中不需要计数（因为我已经有了它），所以我可以测试一下，然后只运行获取前几条记录的存储过程。这只是我的想法。 - PTVee

是的，但是如果另一个用户在页面请求之间添加/删除记录，您想要做什么？ - Bob Vale

嗯？我想这是真的，可能只有一两条记录可能会出现，但是分页可能不准确。 - PTVee

嗨Bob，太棒了！我将两个Select语句放在一个存储过程中，并将第二个Select语句填充到数据集中。因此，虽然我仍在运行两个查询，但已经消除了两次往返。感谢所有在这里提供帮助的人。 - PTVee