在数据库管理中，查询结果的有效限制是优化性能和确保检索相关数据的关键。许多SQL数据库系统，如MySQL和PostgreSQL，使用LIMIT子句来指定查询返回的记录数。然而，SQL Server不支持LIMIT子句，而是使用TOP， OFFSET-FETCH和SET ROWCOUNT这样的替代方案。这种设计体现了SQL Server的灵活性和高性能，它提供了各种方法来实现类似的限制功能，同时满足不同的用例和场景。

让我们仔细看看SQL Server中的LIMIT选项，重点介绍它们的独特功能和限制。

在SQL Server中，SELECT TOP子句可以替代LIMIT子句，可以用于限制查询返回的行数。当你处理大型数据集并且只想检索记录的一个子集时，它特别有用。基本语法是：

SELECT TOP (number | percent) column_names FROM table_name;

这里，number表示要返回的确切行数，而percent是要从总结果集中返回的行数的百分比。根据你的需要使用其中一个参数。

-- 查询返回按雇佣日期的前5名员工SELECT TOP 5 * FROM HumanResources.Employee ORDER BY HireDate;
-- 查询休假超过20小时的前10%员工SELECT TOP 10 PERCENT *FROM HumanResources.Employee WHERE VacationHours > 20;

SELECT TOP不提供随机行。为了实现随机性，可以将其与ORDER BY NEWID() 结合使用，但这对于大型数据集来说效率很低。另一方面，如果不指定ORDER BY子句，结果可能是不可预测的，因为SQL Server不能保证返回行的顺序。

说到分页，SQL Server中可以使用 offset - fetch来实现分页，允许你通过跳过一些行，然后获取定义数量的行来检索特定的记录子集。这个子句的语法如下：

ORDER BY order_by_expression[ COLLATE collation_name ][ ASC | DESC ][ , ...n ][ <offset_fetch> ]<offset_fetch> ::={    OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }    [      FETCH { FIRST | NEXT } { integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY    ]}

OFFSET子句允许你指定在返回行之前需要跳过多少行，而FETCH NEXT定义在跳过行之后要返回多少行。

SELECT * FROM HumanResources.Employee ORDER BY BusinessEntityID OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

我们将页行数、页码作为参数，可以实现动态分页查询。

DECLARE @PageSize INT = 10; DECLARE @PageNumber INT = 5; SELECT BusinessEntityID, JobTitle, HireDate FROM HumanResources.Employee ORDER BY BusinessEntityID OFFSET (@PageSize * (@PageNumber - 1)) ROWS FETCH NEXT @PageSize ROWS ONLY;

OFFSET-FETCH遵循SQL标准，使其可移植并易于开发人员理解。最重要的是，与其他方法（如 ROW_NUMBER()）不同，OFFSET-FETCH直接跳过并获取行，而不需要复杂的变通方法。

但需要注意的是，对于大型数据集，在分页中越往后查询（如第1000页），查询可能会变得越慢，因为SQL Server必须跳过更多行。需要考虑的另一点是，OFFSET-FETCH不返回总行数，因此如果需要显示分页元数据（如总页数），则需要一个额外的查询 count(*) 来获取总行数。记住，当使用OFFSET-FETCH时，ORDER BY子句是强制性的，否则，结果是不可预测的。

在SQL Server中，可以使用SET ROWCOUNT命令来限制SELECT语句返回或受UPDATE或DELETE影响的行数。命令格式如下：

SET ROWCOUNT { number | 0 }

不是指定number，而是指定要返回或处理的行数，0表示重置行计数。

如果你将SET ROWCOUNT和SELECT与其他命令（如ORDER BY和WHERE）一起使用，它们的交互将非常强大。在这种组合中，WHERE子句首先过滤行，ORDER BY子句对过滤的行进行排序，然后SET ROWCOUNT限制从排序的结果集中返回的行数。

下面我们查询前5条记录，然后重置行数限制，以便后续的查询返回所有匹配的行：

SET ROWCOUNT 5; SELECT * FROM HumanResources.Employee WHERE VacationHours > 50 ORDER BY JobTitle; SET ROWCOUNT 0;

SET ROWCOUNT也可以用于DML ，它与select的工作原理类似。

SET ROWCOUNT 1; UPDATE HumanResources.Employee SET JobTitle = 'Chief Stocker' WHERE JobTitle = 'Stocker'; 
SET ROWCOUNT 0; SELECT * FROM HumanResources.Employee WHERE JobTitle LIKE ('%Stocker%') ORDER BY JobTitle;

SET ROWCOUNT提供了一种简单的方法来限制结果，使用非常简单，主要优势有：

• 不需要对原有的SQL进行更改，就可以限制性查询；

• 对整个会话生效，不影响到其他会话；

• 大量数据的DELETE和UPDATE，可以结合@@ROWCOUNT分批分成小事务处理；

注意，SET ROWCOUNT现在越来越不常用了。随着SQL标准的发展，现代SQL实践倾向于对结果集和数据操作进行更显式的控制。因此微软建议使用TOP子句，因为TOP子句提供了更清晰的语义，SET ROWCOUNT在SQL Server 2022版本之后的未来版本中不会影响DELETE和UPDATE语句。

综上所述，让我们简单地比较一下上面讨论过的SQL Server的LIMIT子句：

• SELECT TOP：最适合从结果集中快速检索指定数量的行，特别是在需要固定限制的情况下。对于一些报告或仪表板，其中只需要前N个记录。

• OFFSET-FETCH：最适合大型数据集的分页。它允许你跳过指定数量的行，并返回定义的行集，因此非常适合跨多个页面显示结果。

• SET ROWCOUNT：在旧版本的SQL Server中用于限制行数，或用于更新或删除特定数量的记录。然而，它的使用正在减少，取而代之的是TOP和OFFSET-FETCH。

阅读原文：原文链接