更好的 SQL 分页技术

Question

有没有更好的方法来在SQL服务器中进行分页？

例如，我必须使用以下技术将@skip添加到@take：

;WITH tmp_cte AS (
 SELECT ROW_NUMBER() OVER (ORDER BY LastName) AS RowNumber,
        LastName, 
        FirstName
   FROM person.Person
  WHERE FirstName like '%ad%'
)
SELECT * 
FROM tmp_cte 
WHERE RowNumber > @skip --10 
AND RowNumber <= @Take--20

有没有更好的方法让我不必添加跳过？

Answer 1

在SQL 2012中非常简单：

SELECT LastName, FirstName
  FROM person.Person
 WHERE FirstName like '%ad%'
 ORDER BY LastName
 OFFSET 10 ROWS 
 FETCH NEXT 10 ROWS ONLY;

查看此链接，了解其他版本的 SQL 服务器 + 性能比较。

Answer 2

如果 Person.Person 表上有一个唯一索引（例如，PersonID列上有一个唯一索引，并且此索引是聚集的 - 例如，如果你有一个聚集的 PK），则可以提供更好的解决方案。在这种情况下，上述查询可以重写为：

;WITH tmp_cte AS (
    SELECT ROW_NUMBER() OVER (ORDER BY LastName) AS RowNumber, PersonID
    FROM person.Person
    WHERE FirstName like '%ad%'
)
SELECT ...
FROM Person.Person p
WHERE p.PersonID IN -- PersonID is the key of this UNIQUE INDEX
(
SELECT PersonID FROM tmp_cte 
WHERE RowNumber > @skip 10 AND RowNumber <= @Take--20
)

或

DECLARE @Rows TABLE (ID INT PRIMARY KEY);
;WITH tmp_cte AS (
    SELECT ROW_NUMBER() OVER (ORDER BY LastName) AS RowNumber, PersonID
    FROM person.Person
    WHERE FirstName like '%ad%'
)
INSERT @Rows (ID)
SELECT PersonID FROM tmp_cte 
WHERE RowNumber > @skip 10 AND RowNumber <= @Take--20
SELECT ...
FROM Person.Person p
WHERE p.PersonID IN (SELECT ID FROM @Rows); -- PersonID is the key of this UNIQUE INDEX

注意#1：在这种特殊情况下，(FirstName, LastName)或(LastName, FirstName)的索引也可能很有用。

注意 #2：如果在 final SELECT 子句中有更多列，则此解决方案的性能应该更好。