在SQL Server 2000、2005、2008、2012中分页结果的最佳方法(性能方面)是什么?如果你还想获得结果的总数(在分页之前)?
当前回答
试试这个方法:
SELECT TOP @offset a.*
FROM (select top @limit b.*, COUNT(*) OVER() totalrows
from TABLENAME b order by id asc) a
ORDER BY id desc;
其他回答
http://www.codeproject.com/KB/aspnet/PagingLarge.aspx上有关于不同分页技术的很好的概述
我经常在SQL Server 2000中使用ROWCOUNT方法(也可以在2005和2008中使用,只测量与ROW_NUMBER相比的性能),它的速度非常快,但你需要确保排序的列(大多数)具有唯一的值。
对于ROW_NUMBER技术,如果你没有一个排序列,你可以使用CURRENT_TIMESTAMP,如下所示:
SELECT TOP 20
col1,
col2,
col3,
col4
FROM (
SELECT
tbl.col1 AS col1
,tbl.col2 AS col2
,tbl.col3 AS col3
,tbl.col4 AS col4
,ROW_NUMBER() OVER (
ORDER BY CURRENT_TIMESTAMP
) AS sort_row
FROM dbo.MyTable tbl
) AS query
WHERE query.sort_row > 10
ORDER BY query.sort_row
对于我来说,这种方法在搜索表大小甚至高达700,000的情况下效果很好。
这将获取记录11到30。
CREATE view vw_sppb_part_listsource as
select row_number() over (partition by sppb_part.init_id order by sppb_part.sppb_part_id asc ) as idx, * from (
select
part.SPPB_PART_ID
, 0 as is_rev
, part.part_number
, part.init_id
from t_sppb_init_part part
left join t_sppb_init_partrev prev on ( part.SPPB_PART_ID = prev.SPPB_PART_ID )
where prev.SPPB_PART_ID is null
union
select
part.SPPB_PART_ID
, 1 as is_rev
, prev.part_number
, part.init_id
from t_sppb_init_part part
inner join t_sppb_init_partrev prev on ( part.SPPB_PART_ID = prev.SPPB_PART_ID )
) sppb_part
当涉及到不同的init_id时会重新启动idx吗
试试这个方法:
SELECT TOP @offset a.*
FROM (select top @limit b.*, COUNT(*) OVER() totalrows
from TABLENAME b order by id asc) a
ORDER BY id desc;
用例方面,以下内容似乎易于使用和快速。只需设置页码。
use AdventureWorks
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 6;
with result as(
SELECT SalesOrderDetailID, SalesOrderID, ProductID,
ROW_NUMBER() OVER (ORDER BY SalesOrderDetailID) AS RowNum
FROM Sales.SalesOrderDetail
where 1=1
)
select SalesOrderDetailID, SalesOrderID, ProductID from result
WHERE result.RowNum BETWEEN ((@PageNumber-1)*@RowsPerPage)+1
AND @RowsPerPage*(@PageNumber)
同样没有CTE
use AdventureWorks
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 6
SELECT SalesOrderDetailID, SalesOrderID, ProductID
FROM (
SELECT SalesOrderDetailID, SalesOrderID, ProductID,
ROW_NUMBER() OVER (ORDER BY SalesOrderDetailID) AS RowNum
FROM Sales.SalesOrderDetail
where 1=1
) AS SOD
WHERE SOD.RowNum BETWEEN ((@PageNumber-1)*@RowsPerPage)+1
AND @RowsPerPage*(@PageNumber)
推荐文章
- 如何在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循环在性能上有区别吗?