SQL Server 2008的分页方法？

Question

我必须处理可能很大的记录列表，并且我一直在谷歌搜索避免选择整个列表的方法，相反，我想让用户选择一个页面（例如从 1 到 10）并相应地显示记录。

比如说，对于 1000 条记录，我将有 100 个页面，每页 10 条记录，并且将首先显示最近的 10 条记录，然后如果用户单击第 5 页，它将显示从 41 到 50 的记录。

添加每条记录的行号然后根据行号查询？有没有更好的方法来实现分页结果而不需要太多开销？ 到目前为止，此处描述的这些方法看起来最有前途：

http://developer.berlios。 de/docman/display_doc.php?docid=739&group_id=2899

http://www.codeproject .com/KB/aspnet/PagingLarge.aspx

Answer 1

以下 T-SQL 存储过程是非常高效的分页实现。 SQL优化器可以非常快地找到第一个ID。将此与 ROWCOUNT 的使用结合起来，您将获得一种既具有 CPU 效率又具有读取效率的方法。对于具有大量行的表，它肯定胜过我见过的使用临时表或表变量的任何方法。

注意：我在本例中使用了顺序标识列，但代码适用于任何适合页面排序的列。此外，所使用的列中的序列中断不会影响结果，因为代码选择了许多行而不是列值。

编辑：如果您要对具有潜在非唯一值（例如 LastName）的列进行排序，则将第二列添加到 Order By 子句以使排序值再次唯一。

CREATE  PROCEDURE dbo.PagingTest
(
    @PageNumber int,
    @PageSize int
)
AS

DECLARE @FirstId int, @FirstRow int

SET @FirstRow = ( (@PageNumber - 1) * @PageSize ) + 1
SET ROWCOUNT @FirstRow

-- Add check here to ensure that @FirstRow is not
-- greater than the number of rows in the table.

SELECT   @FirstId = [Id]
FROM     dbo.TestTable
ORDER BY [Id]

SET ROWCOUNT @PageSize

SELECT   *
FROM     dbo.TestTable
WHERE    [Id] >= @FirstId
ORDER BY [Id]

SET ROWCOUNT 0
GO 

Answer 2

如果您使用具有两个 row_number() 列的 CTE - 一列按升序排序，一列降序排序，则通过添加两列 row_number 即可获得用于分页的行号以及总记录。

create procedure get_pages(@page_number int, @page_length int)
as
    set nocount on;

    with cte as
    (
        select 
            Row_Number() over (order by sort_column desc) as row_num
            ,Row_Number() over (order by sort_column) as inverse_row_num
            ,id as cte_id
        From my_table
    )
    Select 
        row_num+inverse_row_num as total_rows
        ,*  
    from CTE inner join my_table
        on cte_id=df_messages.id
    where row_num between 
        (@page_number)*@page_length 
        and (@page_number+1)*@page_length
    order by rownumber

Answer 3

使用OFFSET

其他人已经解释了如何使用ROW_NUMBER() OVER() 排名函数来执行页面。值得一提的是，SQL Server 2012终于包含了对SQL标准的支持 OFFSET .. FETCH 子句：

SELECT first_name, last_name, score
FROM players
ORDER BY score DESC
OFFSET 40 ROWS FETCH NEXT 10 ROWS ONLY

如果您使用的是 SQL Server 2012 并且向后兼容性不是问题，您可能应该更喜欢此子句，因为在特殊情况下 SQL Server 可以更优化地执行它。

使用 SEEK 方法

有一种完全不同、更快的方法可以在 SQL 中执行分页。这通常称为“查找方法”，如 此博客文章位于此处。

SELECT TOP 10 first_name, last_name, score
FROM players
WHERE (score < @previousScore)
   OR (score = @previousScore AND player_id < @previousPlayerId)
ORDER BY score DESC, player_id DESC

@previousScore 和 @previousPlayerId 值是上一页最后一条记录的相应值。这允许您获取“下一页”。如果ORDER BY方向是ASC，则只需使用>即可。

使用上述方法，如果没有先获取前 40 条记录，则无法立即跳转到第 4 页。但通常情况下，您无论如何都不想跳那么远。相反，您可以获得更快的查询，可能能够在恒定时间内获取数据，具体取决于您的索引。另外，无论基础数据是否发生变化，您的页面都保持“稳定”（例如，在第 1 页上，而在第 4 页上）。

例如，当在 Web 应用程序中延迟加载更多数据时，这是实现分页的最佳方法。

请注意，“seek 方法”也称为键集分页。

Answer 4

尝试这样的事情：

declare @page int = 2
declare @size int = 10

declare @lower int =  (@page - 1) * @size
declare @upper int =  (@page    ) * @size

select * from (
select 
    ROW_NUMBER() over (order by some_column) lfd,
* from your_table
) as t
 where lfd between @lower and @upper
 order by some_column

Answer 5

这是 @RoadWarrior 代码的更新版本，使用 TOP。性能相同，而且速度极快。确保您在 TestTable.ID 上有索引

CREATE PROC dbo.PagingTest
    @SkipRows int,
    @GetRows int
AS
DECLARE @FirstId int

SELECT   TOP (@SkipRows) 
         @FirstId = [Id]
FROM     dbo.TestTable
ORDER BY [Id]

SELECT   TOP (@GetRows) *
FROM     dbo.TestTable
WHERE    [Id] >= @FirstId
ORDER BY [Id]

GO 

Answer 6

试试这个

Declare @RowStart int, @RowEnd int;


SET @RowStart = 4;
SET @RowEnd = 7; 

With MessageEntities As 
(
    Select ROW_NUMBER() Over (Order By [MESSAGE_ID]) As Row, [MESSAGE_ID]
    From [TBL_NAFETHAH_MESSAGES]
)
Select  m0.MESSAGE_ID, m0.MESSAGE_SENDER_NAME,
        m0.MESSAGE_SUBJECT, m0.MESSAGE_TEXT
From MessageEntities M
    Inner Join [TBL_NAFETHAH_MESSAGES] m0 on M.MESSAGE_ID = m0.MESSAGE_ID
Where M.Row Between @RowStart AND @RowEnd
Order By M.Row Asc
GO

Answer 7

为什么不使用推荐的

从以下位置选择价值产品
AdventureWorksEntities.Products AS 产品
按产品订购。ListPrice SKIP @skip LIMIT @limit