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.

如果要对可能具有非唯一值(例如LastName)的列进行排序，请在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屋！