SQL Server 2008 分页方法? [英] SQL Server 2008 paging methods?

问题描述

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

I have to work with a potentially large list of records and I've been Googling for ways to avoid selecting the whole list, instead I want to let users select a page (like from 1 to 10) and display the records accordingly.

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

Say, for 1000 records I will have 100 pages of 10 records each and the most recent 10 records will be displayed first then if the user click on page 5, it will show records from 41 to 50.

给每条记录添加一个行号然后根据行号查询是个好主意吗?有没有更好的方法可以在没有太多开销的情况下实现分页结果?到目前为止，这里描述的这些方法看起来最有前途:

Is it a good idea to add a row number to each record then query based on row number? Is there a better way of achieving the paging result without too much overhead? So far those methods as described here look the most promising:

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

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

推荐答案

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

The following T-SQL stored procedure is a very efficient implementation of paging. THE SQL optimiser can find the first ID very fast. Combine this with the use of ROWCOUNT, and you have an approach that is both CPU-efficient and read-efficient. For a table with a large number of rows, it certainly beats any approach that I've seen using a temporary table or table variable.

注意:我在这个例子中使用了一个顺序标识列，但代码适用于任何适合页面排序的列.此外，正在使用的列中的序列中断不会影响结果，因为代码选择的是多行而不是列值.

NB: I'm using a sequential identity column in this example, but the code works on any column suitable for page sorting. Also, sequence breaks in the column being used don't affect the result as the code selects a number of rows rather than a column value.

如果您要对具有潜在非唯一值(例如姓氏)的列进行排序，则将第二列添加到 Order By 子句以使排序值再次唯一.

If you're sorting on a column with potentially non-unique values (eg LastName), then add a second column to the Order By clause to make the sort values unique again.

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 

这篇关于SQL Server 2008 分页方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！