在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 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中不接受变量。

好吧,我已经在我的SQL 2000数据库中使用了以下示例查询,它也适用于SQL 2005。它通过使用多个列来动态排序。 我告诉你……这是强大的:)

    ALTER PROCEDURE [dbo].[RE_ListingReports_SelectSummary] 

@CompanyID  int,
@pageNumber     int,
@pageSize   int, 
@sort       varchar(200)
AS

DECLARE @sql nvarchar(4000)
DECLARE @strPageSize nvarchar(20)
DECLARE @strSkippedRows nvarchar(20)
DECLARE @strFields nvarchar(4000)
DECLARE @strFilter nvarchar(4000)
DECLARE @sortBy nvarchar(4000)
DECLARE @strFrom nvarchar(4000)
DECLARE @strID nvarchar(100)

If(@pageNumber < 0)
  SET @pageNumber = 1
SET @strPageSize = CAST(@pageSize AS varchar(20)) 
SET @strSkippedRows = CAST(((@pageNumber - 1) * @pageSize) AS varchar(20))-- For    example if pageNumber is 5  pageSize is 10, then SkippedRows = 40.
SET @strID = 'ListingDbID'
SET @strFields = 'ListingDbID,
ListingID,  
[ExtraRoom]
'
SET @strFrom = ' vwListingSummary '

SET @strFilter = ' WHERE
        CompanyID = ' + CAST(@CompanyID As varchar(20)) 
End
SET @sortBy = ''
if(len(ltrim(rtrim(@sort))) > 0)
SET @sortBy = ' Order By ' + @sort

-- Total Rows Count

SET @sql =  'SELECT Count(' + @strID + ')  FROM ' + @strFROM + @strFilter
EXEC sp_executesql @sql

--// This technique is used in a Single Table pagination
SET @sql = 'SELECT ' + @strFields + ' FROM ' + @strFROM +
    ' WHERE ' + @strID +  ' IN ' + 
   '  (SELECT TOP ' + @strPageSize + ' ' + @strID + ' FROM ' + @strFROM + @strFilter + 
             ' AND  ' + @strID + ' NOT IN ' + '
          (SELECT TOP ' + @strSkippedRows + ' ' + @strID + ' FROM ' + @strFROM + @strFilter + @SortBy + ') ' 
   + @SortBy + ') ' + @SortBy
Print @sql 
EXEC sp_executesql @sql

最好的部分是sp_executesql缓存以后的调用,只要您传递相同的参数,即生成相同的sql文本。

create PROCEDURE SP_Company_List (@pagesize int= -1,@pageindex int= 0) 选择Id, NameEn从公司订单的Id ASC OFFSET (@pageindex-1)* @pagesize ROWS FETCH NEXt @pagesize ROWS ONLY END GO 声明@return_value int EXEC @return_value = [dbo]。[SP_Company_List] @pagesize = 1, > @pageindex = 2 SELECT 'Return Value' = @return_value 去

这些是我在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;