SQL Server 2008 分页方法？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4358253/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server 2008 paging methods?
提问by fred
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.
我必须处理可能很大的记录列表，我一直在谷歌上搜索避免选择整个列表的方法，相反，我想让用户选择一个页面（例如从 1 到 10）并相应地显示记录。
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.
比如说，对于 1000 条记录，我将有 100 页，每页 10 条记录，最近的 10 条记录将首先显示，然后如果用户单击第 5 页，它将显示从 41 到 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://developer.berlios.de/docman/display_doc.php?docid=739&group_id=2899
回答by HTTP 410
The following T-SQL stored procedure is a veryefficient 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.
下面的 T-SQL 存储过程是一个非常有效的分页实现。SQL 优化器可以非常快地找到第一个 ID。将此与 ROWCOUNT 的使用相结合，您将拥有一种既 CPU 高效又读取高效的方法。对于包含大量行的表，它肯定胜过我见过的任何使用临时表或表变量的方法。
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.
注意：我在这个例子中使用了一个顺序标识列，但代码适用于任何适合页面排序的列。此外，正在使用的列中的序列中断不会影响结果，因为代码选择的是多行而不是列值。
EDIT: 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.
编辑：如果您对具有潜在非唯一值（例如姓氏）的列进行排序，则将第二列添加到 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
回答by 3Dave
If you use a CTE with two row_number() columns - one sorted asc, one desc, you get row numbers for paging as well as the total records by adding the two row_number columns.
如果您使用带有两个 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
回答by Lukas Eder
Using OFFSET
使用偏移
Others have explained how the ROW_NUMBER() OVER()ranking function can be used to perform pages. It's worth mentioning that SQL Server 2012 finally included support for the SQL standard OFFSET .. FETCHclause:
其他人已经解释了如何使用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
If you're using SQL Server 2012 and backwards-compatibility is not an issue, you should probably prefer this clause as it will be executed more optimally by SQL Server in corner cases.
如果您使用的是 SQL Server 2012 并且向后兼容性不是问题，您可能应该更喜欢这个子句，因为它会在极端情况下由 SQL Server 以最佳方式执行。
Using the SEEK Method
使用 SEEK 方法
There is an entirely different, much faster way to perform paging in SQL. This is often called the "seek method" as described in this blog post here.
有一种完全不同的、更快的方式在 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
The @previousScoreand @previousPlayerIdvalues are the respective values of the last record from the previous page. This allows you to fetch the "next" page. If the ORDER BYdirection is ASC, simply use >instead.
该@previousScore和@previousPlayerId值是来自前一页的最后一条记录的相应值。这允许您获取“下一个”页面。如果ORDER BY方向是ASC，只需使用>。
With the above method, you cannot immediately jump to page 4 without having first fetched the previous 40 records. But often, you do not want to jump that far anyway. Instead, you get a much faster query that might be able to fetch data in constant time, depending on your indexing. Plus, your pages remain "stable", no matter if the underlying data changes (e.g. on page 1, while you're on page 4).
使用上面的方法，你不能在没有先获取前 40 条记录的情况下立即跳转到第 4 页。但通常情况下，您无论如何都不想跳那么远。相反，您会得到一个更快的查询，它可能能够在恒定时间内获取数据，具体取决于您的索引。此外，无论基础数据是否发生变化（例如，在第 1 页上，而在第 4 页上），您的页面都将保持“稳定”。
This is the best way to implement paging when lazy loading more data in web applications, for instance.
例如，当在 Web 应用程序中延迟加载更多数据时，这是实现分页的最佳方式。
Note, the "seek method" is also called keyset paging.
请注意，“seek 方法”也称为keyset paging。
回答by bernd_k
Try something like this:
尝试这样的事情：
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
回答by Lane
Here's an updated version of @RoadWarrior's code, using TOP. Performance is identical, and extremely fast. Make sure you have an index on TestTable.ID
这是@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
回答by Vladimir Sosnin
回答by Aalkhodiry
Try this
尝试这个
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
