在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组合使用 表达式。
其他回答
令人难以置信的是，没有其他答案提到在所有SQL Server版本中进行分页的最快方法。对于大页码，偏移量可能非常慢。在SQL中有一种完全不同的更快的分页方法。这通常被称为“查找方法”或“键集分页”，如本文所述。
SELECT TOP 10 first_name, last_name, score, COUNT(*) OVER()
FROM players
WHERE (score < @previousScore)
OR (score = @previousScore AND player_id < @previousPlayerId)
ORDER BY score DESC, player_id DESC
"seek谓词"
@previousScore和@previousPlayerId值分别是前一页中最后一条记录的值。这允许你获取“下一页”。如果ORDER BY方向为ASC，只需使用>即可。
使用上述方法，如果不先获取之前的40条记录，就不能立即跳转到第4页。但通常情况下，你并不想跳得那么远。相反，您将获得一个更快的查询，可能能够在常数时间内获取数据，这取决于您的索引。另外，你的页面保持“稳定”，无论底层数据是否发生变化(例如，在第1页，而你在第4页)。
例如，当在web应用程序中延迟加载更多数据时，这是实现分页的最佳方法。
注意，“seek方法”也称为键集分页。
分页前的记录总数
COUNT(*) OVER()窗口函数将帮助您计算“分页前”的总记录数量。如果您使用的是SQL Server 2000，则必须对COUNT(*)进行两次查询。
在SQL Server 2012中，我们可以使用OFFSET和FETCH NEXT子句来实现分页。
试试这个，SQL Server:
In the SQL Server 2012 a new feature was added in the ORDER BY clause, to query optimization of a set data, making work easier with data paging for anyone who writes in T-SQL as well for the entire Execution Plan in SQL Server. Below the T-SQL script with the same logic used in the previous example. --CREATING A PAGING WITH OFFSET and FETCH clauses IN "SQL SERVER 2012" DECLARE @PageNumber AS INT, @RowspPage AS INT SET @PageNumber = 2 SET @RowspPage = 10 SELECT ID_EXAMPLE, NM_EXAMPLE, DT_CREATE FROM TB_EXAMPLE ORDER BY ID_EXAMPLE OFFSET ((@PageNumber - 1) * @RowspPage) ROWS FETCH NEXT @RowspPage ROWS ONLY;
使用SQL Server对查询进行分页
从2012年起，我们可以使用 偏移10行只获取下10行
这些是我在SQL server端对查询结果进行分页的解决方案。 这些方法在SQL Server 2008和2012之间是不同的。 此外，我还添加了过滤和排序的概念。当你在Gridview中分页、过滤和排序时，它是非常高效的。
在测试之前，你必须创建一个示例表，并在这个表中插入一些行:(在现实世界中，你必须改变Where子句，考虑到你的表字段，也许你有一些连接和子查询在select的主要部分)
Create Table VLT
(
ID int IDentity(1,1),
Name nvarchar(50),
Tel Varchar(20)
)
GO
Insert INTO VLT
VALUES
('NAME' + Convert(varchar(10),@@identity),'FAMIL' + Convert(varchar(10),@@identity))
GO 500000
在所有这些示例中，我想查询每页200行，我正在获取页码为1200的行。
在SQL server 2008中，您可以使用CTE概念。因此，我为SQL server 2008+编写了两种类型的查询
SQL Server 2008+
DECLARE @PageNumber Int = 1200
DECLARE @PageSize INT = 200
DECLARE @SortByField int = 1 --The field used for sort by
DECLARE @SortOrder nvarchar(255) = 'ASC' --ASC or DESC
DECLARE @FilterType nvarchar(255) = 'None' --The filter type, as defined on the client side (None/Contain/NotContain/Match/NotMatch/True/False/)
DECLARE @FilterValue nvarchar(255) = '' --The value the user gave for the filter
DECLARE @FilterColumn int = 1 --The column to wich the filter is applied, represents the column number like when we send the information.
SELECT
Data.ID,
Data.Name,
Data.Tel
FROM
(
SELECT
ROW_NUMBER()
OVER( ORDER BY
CASE WHEN @SortByField = 1 AND @SortOrder = 'ASC'
THEN VLT.ID END ASC,
CASE WHEN @SortByField = 1 AND @SortOrder = 'DESC'
THEN VLT.ID END DESC,
CASE WHEN @SortByField = 2 AND @SortOrder = 'ASC'
THEN VLT.Name END ASC,
CASE WHEN @SortByField = 2 AND @SortOrder = 'DESC'
THEN VLT.Name END ASC,
CASE WHEN @SortByField = 3 AND @SortOrder = 'ASC'
THEN VLT.Tel END ASC,
CASE WHEN @SortByField = 3 AND @SortOrder = 'DESC'
THEN VLT.Tel END ASC
) AS RowNum
,*
FROM VLT
WHERE
( -- We apply the filter logic here
CASE
WHEN @FilterType = 'None' THEN 1
-- Name column filter
WHEN @FilterType = 'Contain' AND @FilterColumn = 1
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.ID LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'NotContain' AND @FilterColumn = 1
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.ID NOT LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'Match' AND @FilterColumn = 1
AND VLT.ID = @FilterValue THEN 1
WHEN @FilterType = 'NotMatch' AND @FilterColumn = 1
AND VLT.ID <> @FilterValue THEN 1
-- Name column filter
WHEN @FilterType = 'Contain' AND @FilterColumn = 2
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.Name LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'NotContain' AND @FilterColumn = 2
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.Name NOT LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'Match' AND @FilterColumn = 2
AND VLT.Name = @FilterValue THEN 1
WHEN @FilterType = 'NotMatch' AND @FilterColumn = 2
AND VLT.Name <> @FilterValue THEN 1
-- Tel column filter
WHEN @FilterType = 'Contain' AND @FilterColumn = 3
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.Tel LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'NotContain' AND @FilterColumn = 3
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.Tel NOT LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'Match' AND @FilterColumn = 3
AND VLT.Tel = @FilterValue THEN 1
WHEN @FilterType = 'NotMatch' AND @FilterColumn = 3
AND VLT.Tel <> @FilterValue THEN 1
END
) = 1
) AS Data
WHERE Data.RowNum > @PageSize * (@PageNumber - 1)
AND Data.RowNum <= @PageSize * @PageNumber
ORDER BY Data.RowNum
GO
在SQL server 2008+中使用CTE的第二个解决方案
DECLARE @PageNumber Int = 1200
DECLARE @PageSize INT = 200
DECLARE @SortByField int = 1 --The field used for sort by
DECLARE @SortOrder nvarchar(255) = 'ASC' --ASC or DESC
DECLARE @FilterType nvarchar(255) = 'None' --The filter type, as defined on the client side (None/Contain/NotContain/Match/NotMatch/True/False/)
DECLARE @FilterValue nvarchar(255) = '' --The value the user gave for the filter
DECLARE @FilterColumn int = 1 --The column to wich the filter is applied, represents the column number like when we send the information.
;WITH
Data_CTE
AS
(
SELECT
ROW_NUMBER()
OVER( ORDER BY
CASE WHEN @SortByField = 1 AND @SortOrder = 'ASC'
THEN VLT.ID END ASC,
CASE WHEN @SortByField = 1 AND @SortOrder = 'DESC'
THEN VLT.ID END DESC,
CASE WHEN @SortByField = 2 AND @SortOrder = 'ASC'
THEN VLT.Name END ASC,
CASE WHEN @SortByField = 2 AND @SortOrder = 'DESC'
THEN VLT.Name END ASC,
CASE WHEN @SortByField = 3 AND @SortOrder = 'ASC'
THEN VLT.Tel END ASC,
CASE WHEN @SortByField = 3 AND @SortOrder = 'DESC'
THEN VLT.Tel END ASC
) AS RowNum
,*
FROM VLT
WHERE
( -- We apply the filter logic here
CASE
WHEN @FilterType = 'None' THEN 1
-- Name column filter
WHEN @FilterType = 'Contain' AND @FilterColumn = 1
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.ID LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'NotContain' AND @FilterColumn = 1
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.ID NOT LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'Match' AND @FilterColumn = 1
AND VLT.ID = @FilterValue THEN 1
WHEN @FilterType = 'NotMatch' AND @FilterColumn = 1
AND VLT.ID <> @FilterValue THEN 1
-- Name column filter
WHEN @FilterType = 'Contain' AND @FilterColumn = 2
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.Name LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'NotContain' AND @FilterColumn = 2
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.Name NOT LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'Match' AND @FilterColumn = 2
AND VLT.Name = @FilterValue THEN 1
WHEN @FilterType = 'NotMatch' AND @FilterColumn = 2
AND VLT.Name <> @FilterValue THEN 1
-- Tel column filter
WHEN @FilterType = 'Contain' AND @FilterColumn = 3
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.Tel LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'NotContain' AND @FilterColumn = 3
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.Tel NOT LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'Match' AND @FilterColumn = 3
AND VLT.Tel = @FilterValue THEN 1
WHEN @FilterType = 'NotMatch' AND @FilterColumn = 3
AND VLT.Tel <> @FilterValue THEN 1
END
) = 1
)
SELECT
Data.ID,
Data.Name,
Data.Tel
FROM Data_CTE AS Data
WHERE Data.RowNum > @PageSize * (@PageNumber - 1)
AND Data.RowNum <= @PageSize * @PageNumber
ORDER BY Data.RowNum
SQL Server 2012+
DECLARE @PageNumber Int = 1200
DECLARE @PageSize INT = 200
DECLARE @SortByField int = 1 --The field used for sort by
DECLARE @SortOrder nvarchar(255) = 'ASC' --ASC or DESC
DECLARE @FilterType nvarchar(255) = 'None' --The filter type, as defined on the client side (None/Contain/NotContain/Match/NotMatch/True/False/)
DECLARE @FilterValue nvarchar(255) = '' --The value the user gave for the filter
DECLARE @FilterColumn int = 1 --The column to wich the filter is applied, represents the column number like when we send the information.
;WITH
Data_CTE
AS
(
SELECT
*
FROM VLT
WHERE
( -- We apply the filter logic here
CASE
WHEN @FilterType = 'None' THEN 1
-- Name column filter
WHEN @FilterType = 'Contain' AND @FilterColumn = 1
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.ID LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'NotContain' AND @FilterColumn = 1
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.ID NOT LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'Match' AND @FilterColumn = 1
AND VLT.ID = @FilterValue THEN 1
WHEN @FilterType = 'NotMatch' AND @FilterColumn = 1
AND VLT.ID <> @FilterValue THEN 1
-- Name column filter
WHEN @FilterType = 'Contain' AND @FilterColumn = 2
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.Name LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'NotContain' AND @FilterColumn = 2
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.Name NOT LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'Match' AND @FilterColumn = 2
AND VLT.Name = @FilterValue THEN 1
WHEN @FilterType = 'NotMatch' AND @FilterColumn = 2
AND VLT.Name <> @FilterValue THEN 1
-- Tel column filter
WHEN @FilterType = 'Contain' AND @FilterColumn = 3
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.Tel LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'NotContain' AND @FilterColumn = 3
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.Tel NOT LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'Match' AND @FilterColumn = 3
AND VLT.Tel = @FilterValue THEN 1
WHEN @FilterType = 'NotMatch' AND @FilterColumn = 3
AND VLT.Tel <> @FilterValue THEN 1
END
) = 1
)
SELECT
Data.ID,
Data.Name,
Data.Tel
FROM Data_CTE AS Data
ORDER BY
CASE WHEN @SortByField = 1 AND @SortOrder = 'ASC'
THEN Data.ID END ASC,
CASE WHEN @SortByField = 1 AND @SortOrder = 'DESC'
THEN Data.ID END DESC,
CASE WHEN @SortByField = 2 AND @SortOrder = 'ASC'
THEN Data.Name END ASC,
CASE WHEN @SortByField = 2 AND @SortOrder = 'DESC'
THEN Data.Name END ASC,
CASE WHEN @SortByField = 3 AND @SortOrder = 'ASC'
THEN Data.Tel END ASC,
CASE WHEN @SortByField = 3 AND @SortOrder = 'DESC'
THEN Data.Tel END ASC
OFFSET @PageSize * (@PageNumber - 1) ROWS FETCH NEXT @PageSize ROWS ONLY;
在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
推荐文章
- 如果性能很重要，我应该使用Java的String.format()吗?
- 使用“var”会影响性能吗?
- 在Python中哪个更快:x**。5还是math.sqrt(x)?
- django test app error -在创建测试数据库时出现错误:创建数据库的权限被拒绝
- 在Android SQLite中处理日期的最佳方法
- 如何在MySQL表中移动列?
- 在SQL Server中查找重复的行
- 执行带有Output参数的存储过程?
- 从DateTime中提取小时(SQL Server 2005)
- 我如何在T-SQL用逗号格式化一个数字?
- LEFT OUTER JOIN如何返回比左表中存在的记录更多的记录?
- 如何用SQL语句计算百分比
- 数组与列表的性能
- 使用私有静态方法的优点
- Postgres唯一约束与索引
