65.9K
CodeProject 正在变化。 阅读更多。
Home

SQL Server 代号为“Denali”的临时分页支持

mazhou
starIconstarIconstarIconstarIconstarIcon

5.00/5 (1投票)

2010年11月12日

Ms-PL

4分钟阅读

viewsIcon

11659

SQL Server 代号为“Denali”的临时分页支持

微软昨天发布了其下一代 SQL Server 产品（代号“Denali”）的首个社区技术预览版 (CTP)。SQL Server “Denali” 带来了许多新功能和改进，其中一些对可编程性和可管理性方面的改进您可能会感兴趣：

  • 基于 Visual Studio 2010 的管理工具
  • 高级 T-SQL 调试支持
  • 代码片段和 IntelliSense 增强
  • 独立数据库
  • 增强的 EXECUTE 语句和 RESULT SETS
  • ORDER BY 子句的 OFFSET 和 FETCH 子句
  • SEQUENCE 对象
  • 增强的 **geometry** 数据类型

要下载 SQL Server “Denali” CTP 1，请访问 Microsoft 下载中心上的此页面：http://www.microsoft.com/downloads/en/details.aspx?FamilyID=6a04f16f-f6be-4f92-9c92-f7e5677d91f9。

在本文中，我将讨论使用 SELECT、ORDER BY 和 OFFSET 查询实现的即席分页支持。

问题

在以数据为中心的 Web 或 Windows 应用程序中，开发人员的常见任务之一是从数据库中查找满足指定查询条件的结果集，然后在用户界面中显示该结果集。假设应用程序的目标是 Windows Phone 7 平台，并且结果集很大，则用户界面可能没有足够的空间在一帧中渲染所有数据。因此，开发人员必须为用户界面添加一个垂直滚动条，以便最终用户可以上下滚动以查看其余数据行；此外，如果结果集非常庞大，滚动条可能会很短，用户需要很长时间才能从顶部滚动到底部，用户可能还难以在长列表中找到所需内容。在这种情况下，必须将结果集拆分成页面，以便垂直滚动条更长，这样应用程序就更容易导航到用户想要的数据。通过使用分页，应用程序可以更易于使用且响应更快。这就是所谓的 **分页**。

假设我们要使用 SQL Server 数据库进行分页，在 SQL Server “Denali” 之前，没有直接支持分页，社区中通常使用以下方法：

  1. 使用 TOP 和 EXCEPT
  2. 使用 ROW_NUMBER 和 OVER 结合公共表表达式 (CTE)

使用 TOP 和 EXCEPT

以下 T-SQL 代码展示了如何使用 TOP 和 EXCEPT 结合 SELECT 查询来实现分页。

USE TechTalk;
GO
 
-- The page number and the page size variables
-- for demo, I hardcoded them with initial values.
-- @pageNumber must greater than or equal to 1.
DECLARE @pageSize int = 10;
DECLARE @pageNumber int = 2;
 
SELECT TOP (@pageSize * @pageNumber) *
FROM dbo.[Event]
EXCEPT
SELECT TOP (@pageSize * (@pageNumber - 1)) *
FROM dbo.[Event];
GO

算法如下：SELECT TOP 语句的第一部分查找数据，其大小等于 @pageSize 乘以 @pageNumber。这将选择所有前页和当前页的数据，然后使用 EXCEPT 投影，通过使用另一个 SELECT TOP 语句（大小等于 @pageSize 乘以 @pageNumber 减去 1）来消除前页数据。结果集是从 (@pageSize * (@pageNumber – 1) + 1) 开始，到 (@pageSize * @pageNumber) 结束。

当我在 SQL Server Management Studio 中运行此代码时，我得到了以下输出：

image

使用 ROW_NUMBER() 和 OVER 结合公共表表达式

实际上，如果查找表为每行提供了一个标识号，那么分页问题就不再是一个大问题，因为您可以计算指定页码的起始行号和结束行号，然后使用 WHERE 子句过滤查询。

为了实现这一点，SQL Server 提供了一个 ROW_NUMBER() 函数，可用于生成动态行标识号（从 1 开始，增量为 1）；通过公共表表达式，您可以轻松地将此行号列临时添加到查找表的 Select 结果集中，然后使用 WHERE 子句来实现分页。

以下 T-SQL 代码展示了如何使用 ROW_NUMBER() 进行分页。

USE TechTalk;
GO
 
-- The page number and the page size variables
-- for demo, I hardcoded them with initial values.
-- @pageNumber must greater than or equal to 1.
DECLARE @pageSize int = 10;
DECLARE @pageNumber int = 2;
 
WITH TempEvent AS
(
	SELECT *, ROW_NUMBER() OVER (ORDER BY Id) AS RowNumber
	FROM dbo.[Event]
)
SELECT * FROM TempEvent
WHERE RowNumber BETWEEN (@pageSize * (@pageNumber - 1) + 1)
AND (@pageSize * @pageNumber)

当我运行此代码时，我得到的结果与第一个示例完全相同。

使用 ORDER BY 子句的 OFFSET 和 FETCH

SQL Server “Denali” 通过添加 OFFSET 和 FETCH 子句来增强 ORDER BY 子句，以指定要跳过的行数和要返回的行数。ORDER BY 的增强语法为：

ORDER BY order_by_expression
     [ COLLATE collation_name ]
     [ ASC | DESC ]
     [ ,...n ] 
[ <offset_fetch> ]

<offset_fetch> ::=
{    OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }
     [
       FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression }
        { ROW | ROWS } ONLY
     ]
}

以下 T-SQL 代码展示了 OFFSET 和 FETCH 结合 ORDER BY 来实现分页的用法。

USE TechTalk;
GO
 
-- The page number and the page size variables
-- for demo, I hardcoded them with initial values.
-- @pageNumber must greater than or equal to 1.
DECLARE @pageSize int = 10;
DECLARE @pageNumber int = 2;
 
SELECT *
FROM dbo.[Event]
ORDER BY CreatedTime DESC
OFFSET @pageSize * (@pageNumber - 1) ROWS
FETCH NEXT @pageSize ROWS ONLY;

当我运行此代码时，我得到了以下输出：

image

现在，您拥有了一种极其简单的方式来在您的应用程序中实现分页！

注意：ADO.NET Entity Framework、LINQ to SQL 和 LINQ to Object 通过使用 Skip() 和 Take() 扩展方法支持分页。对于当前的 Entity Framework 和 LINQ to SQL 实现，可以生成使用 ROW_NUMBER() 函数的 T-SQL，它不会生成新的 OFFSET 和 FETCH T-SQL 代码，直到 Entity Framework 和 LINQ to SQL 提供程序在 .NET Framework 的未来版本中进行更改。可以预见，在即将到来的 .NET Framework 5.0 和 Entity Framework 5.0 中，SQL Server “Denali” 与它们的集成将在 ADO.NET 数据访问技术中扮演重要角色。

© . All rights reserved.