SQL Server中分页查询的多种写法比较

分页查询在SQL Server中是一个非常常见的需求，尤其是在处理大量数据时。下面我们将讨论几种常见的分页查询方法，并分析它们的优缺点。

方法一：使用TOP和NOT IN

这是最基础的一种方式，通过SELECT TOP N和NOT IN来实现分页。

SELECT TOP 10 * 
FROM table_name 
WHERE id NOT IN (SELECT TOP 20 id FROM table_name ORDER BY id) 
ORDER BY id;

这种方法简单易懂，但在大规模数据集上性能较差，因为NOT IN可能导致全表扫描。

方法二：使用ROW_NUMBER()

ROW_NUMBER()函数可以为每一行分配一个唯一的序号，从而实现分页。

WITH CTE AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS RowNum
    FROM table_name
)
SELECT *
FROM CTE
WHERE RowNum BETWEEN 21 AND 30;

这种方式性能较好，尤其是当数据量增大时，因为它避免了重复扫描的问题。

方法三：使用OFFSET和FETCH

从SQL Server 2012开始，支持OFFSET和FETCH语法，这是标准SQL的一部分，用于直接指定要跳过的行数和获取的行数。

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

这种写法简洁明了，且性能优秀，特别适合于现代SQL Server版本。

性能比较

• TOP和NOT IN: 在小数据集上表现尚可，但随着数据量增加，性能会显著下降。
• ROW_NUMBER(): 对于中等规模的数据集，性能良好，但需要额外的计算来生成行号。
• OFFSET和FETCH: 提供了最佳的性能和可读性，推荐在支持的环境中使用。

注意事项

• 分页查询时一定要有明确的排序规则，否则结果可能不一致。
• 避免在大数据集上使用可能导致全表扫描的操作符，如NOT IN。

flowchart TD
    A[开始] --> B{选择方法}
    B -->|TOP和NOT IN| C[适用于小数据集]
    B -->|ROW_NUMBER()| D[适用于中等数据集]
    B -->|OFFSET和FETCH| E[适用于所有数据集]
    C --> F[结束]
    D --> F
    E --> F