[SQL Server]SQL Server中有哪些更好的分页的方法呢？

问题描述

如题，我们从数据库读取数据，特别是读取大批量数据时需要使用分页功能，那么，在SQL Server 2005、SQL Server 2008、SQL Server 2012等数据库中，有哪些更高效的分页写法呢，包括分页时返回数据的总数？

方案一

在SQL Server 2005或者以上版本中，我们可以使用ROW_NUMBER()函数，如：

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
          FROM      Orders
          WHERE     OrderDate >= '1980-01-01'
        ) AS RowConstrainedResult
WHERE   RowNum >= 1
    AND RowNum < 20
ORDER BY RowNum

要获取数据记录总数，则需要使用COUNT(*) 函数，如：

SELECT COUNT(*) FROM Orders WHERE OrderDate >= '1980-01-01'

方案二

在SQL Server 2012或者以上版本中，我们可以使用OFFSET FETCH NEXT语法可快速地实现SQL Server数据库分页功能，如：

SELECT * FROM TableName ORDER BY id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

方案三

使用CTE功能，如：

DECLARE @START_ROW int, @MAX_ROWS int, @TOT_ROW_CNT int
SELECT @START_ROW = 1, @MAX_ROWS = 25;

WITH PAGED AS (
            SELECT ROW_NUMBER() OVER(ORDER BY MyDate, MYID) AS rowNum,     *
            FROM TRANS_TABLE (NOLOCK)
            )
SELECT *
FROM PAGED
WHERE ROWNUM BETWEEN @START_ROW AND @START_ROW + @MAX_ROWS-1

方案四

另外一种CTE的实现方式，如：

DECLARE @START_ROW int, @MAX_ROWS int, @TOT_ROW_CNT int
SELECT @START_ROW = 1, @MAX_ROWS = 25;

WITH PAGED AS (
            SELECT ROW_NUMBER() OVER(ORDER BY MyDate, MYID) AS rowNum, MYID
            FROM TRANS_TABLE (NOLOCK)
            )
SELECT TT.*
FROM PAGED PGD
INNER JOIN TRANS_TABLE TT
ON PGD.MYID = TT.MYID
WHERE ROWNUM BETWEEN @START_ROW AND @START_ROW + @MAX_ROWS - 1
ORDER BY MyDate, MYID

方案五

使用临时表实现分页功能，如：

DECLARE @START_ROW int, @MAX_ROWS int, @TOT_ROW_CNT int
SELECT @START_ROW = 1, @MAX_ROWS = 25;

    SELECT ROW_NUMBER() OVER(ORDER BY MyDate, MYID) AS rowNum, 
        MYID
    into #TEMP
    FROM TRANS_TABLE (NOLOCK)

SELECT TT.*
FROM TRANS_TABLE (NOLOCK) TT
INNER JOIN #TEMP T
ON TT.MYID = T.MYID
WHERE ROWNUM BETWEEN @START_ROW AND @START_ROW + @MAX_ROWS - 1

DROP TABLE #TEMP