在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组合使用 表达式。
其他回答
试试这个方法:
SELECT TOP @offset a.*
FROM (select top @limit b.*, COUNT(*) OVER() totalrows
from TABLENAME b order by id asc) a
ORDER BY id desc;
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
http://www.codeproject.com/KB/aspnet/PagingLarge.aspx上有关于不同分页技术的很好的概述
我经常在SQL Server 2000中使用ROWCOUNT方法(也可以在2005和2008中使用,只测量与ROW_NUMBER相比的性能),它的速度非常快,但你需要确保排序的列(大多数)具有唯一的值。
最后,微软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组合使用 表达式。
对于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中不接受变量。
推荐文章
- 如何在Ruby On Rails中使用NuoDB手动执行SQL命令
- 查询JSON类型内的数组元素
- 确定记录是否存在的最快方法
- 阅读GHC核心
- 获得PostgreSQL数据库中当前连接数的正确查询
- 在SQL选择语句Order By 1的目的是什么?
- Python: List vs Dict用于查找表
- 从现有模式生成表关系图(SQL Server)
- 我如何循环通过一组记录在SQL Server?
- 为什么MATLAB的矩阵乘法运算这么快?
- 数据库和模式的区别
- 如何在SQL Server中一次更改多个列
- 如何从命令行通过mysql运行一个查询?
- 外键约束可能导致循环或多条级联路径?
- for循环和for-each循环在性能上有区别吗?