在SQL Server 2000、2005、2008、2012中分页结果的最佳方法(性能方面)是什么?如果你还想获得结果的总数(在分页之前)?
当前回答
最后,微软SQL Server 2012发布了,我真的很喜欢它的简单的分页,你不需要使用复杂的查询,就像这里回答的。
要获得接下来的10行,只需运行这个查询:
SELECT * FROM TableName ORDER BY id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
https://learn.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql#using-offset-and-fetch-to-limit-the-rows-returned
使用时需要考虑的要点:
ORDER BY是强制使用OFFSET…获取子句。 OFFSET子句在FETCH中是强制的。你不能使用ORDER BY… 取回。 在同一个查询中,TOP不能与OFFSET和FETCH组合使用 表达式。
其他回答
MSDN: ROW_NUMBER (Transact-SQL)
返回结果集中分区内某一行的顺序编号,每个分区中的第一行从1开始。 下面的示例按照OrderDate的顺序返回数字为50到60的行。
WITH OrderedOrders AS
(
SELECT
ROW_NUMBER() OVER(ORDER BY FirstName DESC) AS RowNumber,
FirstName, LastName, ROUND(SalesYTD,2,1) AS "Sales YTD"
FROM [dbo].[vSalesPerson]
)
SELECT RowNumber,
FirstName, LastName, Sales YTD
FROM OrderedOrders
WHERE RowNumber > 50 AND RowNumber < 60;
RowNumber FirstName LastName SalesYTD
--- ----------- ---------------------- -----------------
1 Linda Mitchell 4251368.54
2 Jae Pak 4116871.22
3 Michael Blythe 3763178.17
4 Jillian Carson 3189418.36
5 Ranjit Varkey Chudukatil 3121616.32
6 José Saraiva 2604540.71
7 Shu Ito 2458535.61
8 Tsvi Reiter 2315185.61
9 Rachel Valdez 1827066.71
10 Tete Mensa-Annan 1576562.19
11 David Campbell 1573012.93
12 Garrett Vargas 1453719.46
13 Lynn Tsoflias 1421810.92
14 Pamela Ansman-Wolfe 1352577.13
对于SQL Server 2000,您可以使用一个带有IDENTITY列的表变量来模拟ROW_NUMBER():
DECLARE @pageNo int -- 1 based
DECLARE @pageSize int
SET @pageNo = 51
SET @pageSize = 20
DECLARE @firstRecord int
DECLARE @lastRecord int
SET @firstRecord = (@pageNo - 1) * @pageSize + 1 -- 1001
SET @lastRecord = @firstRecord + @pageSize - 1 -- 1020
DECLARE @orderedKeys TABLE (
rownum int IDENTITY NOT NULL PRIMARY KEY CLUSTERED,
TableKey int NOT NULL
)
SET ROWCOUNT @lastRecord
INSERT INTO @orderedKeys (TableKey) SELECT ID FROM Orders WHERE OrderDate >= '1980-01-01' ORDER BY OrderDate
SET ROWCOUNT 0
SELECT t.*
FROM Orders t
INNER JOIN @orderedKeys o ON o.TableKey = t.ID
WHERE o.rownum >= @firstRecord
ORDER BY o.rownum
这种方法可以扩展到具有多列键的表,并且不会引起使用OR(跳过索引使用)的性能开销。缺点是,如果数据集非常大,并且其中一个接近最后一页,则会占用大量临时空间。在这种情况下,我没有测试游标的性能,但它可能会更好。
注意,这种方法可以针对数据的第一页进行优化。另外,使用ROWCOUNT是因为TOP在SQL Server 2000中不接受变量。
从2012年起,我们可以使用 偏移10行只获取下10行
您没有指定使用的语言或驱动程序。因此我只是抽象地描述它。
创建可滚动的结果集/数据集。这要求在表上有一个(多个)主表 跳到最后 请求行数 跳转到页面的开头 滚动行直到页面的末尾
在sql server 2012中分页的最佳方法是在存储过程中使用offset和fetch next。 OFFSET关键字-如果对order by子句使用OFFSET,则查询将跳过OFFSET n Rows中指定的记录数。
FETCH NEXT关键字-当我们使用FETCH NEXT与一个order by子句,它将返回你想要显示在分页的行数,没有Offset,那么SQL将生成一个错误。 下面是一个例子。
create procedure sp_paging
(
@pageno as int,
@records as int
)
as
begin
declare @offsetcount as int
set @offsetcount=(@pageno-1)*@records
select id,bs,variable from salary order by id offset @offsetcount rows fetch Next @records rows only
end
您可以像下面这样执行它。
exec sp_paging 2,3
推荐文章
- SQL Server:过滤sp_who2的输出
- 使用SQL查询查找最近的纬度/经度
- 在SQL Server上使用varchar(MAX) vs TEXT
- Visual Studio: ContextSwitchDeadlock
- Sql Server字符串到日期的转换
- .toArray(new MyClass[0]) or .toArray(new MyClass[myList.size()])?
- 将一列的多个结果行连接为一列,按另一列分组
- 检查MySQL表是否存在而不使用“select from”语法?
- 是什么导致JNI调用变慢?
- 如何将SQL Azure数据库复制到本地开发服务器?
- 检查属性是否有属性
- SQL Server 2008不能用新创建的用户登录
- 如何快速清除JavaScript对象?
- 在PostgreSQL中快速发现表的行数
- 更改varchar列的大小为较低的长度