【实用技能】在 SQL Server 中使用 LIMIT 子句的替代方案

在数据库管理中，有效限制查询结果对于优化性能和确保检索相关数据至关重要。许多 SQL 数据库系统（例如 MySQL 和 PostgreSQL）都使用LIMIT子句来指定查询返回的记录数。但是，SQL Server 不支持该LIMIT子句，而是选择诸如TOP、和之类的替代方案。这种设计选择反映了 SQL Server 对灵活性和性能的关注，提供了各种方法来实现类似的功能，同时满足不同的用例和场景。

让我们仔细看看LIMITSQL Server 中的替代方案，重点介绍它们的独特功能和局限性。

dbForge Studio for SQL Server官方正版下载

使用 SELECT TOP 子句

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

SELECT TOP (number | percent) column_names
FROM table_name;

此处，number代表要返回的确切行数，是percent返回的行数占总结果集的百分比。请根据需要使用这些参数之一。

您可以通过添加其他子句（比如WHERE or ORDER BY ）来进一步优化结果。

例如，以下查询返回按受雇日期排序的前五名员工（此处和下面，我们将在示例中使用 AdventureWorks2022 示例数据库）：

USE AdventureWorks2022;
SELECT TOP 5 *
FROM HumanResources.Employee
ORDER BY HireDate;

或者，此查询检索休假时间超过 20 小时的前 10％员工的国家 ID 和职位：

USE AdventureWorks2022;
SELECT TOP 10 PERCENT NationalIDNumber, JobTitle
FROM HumanResources.Employee
WHERE VacationHours > 20;

使用该SELECT TOP子句有很多好处。首先，性能优化——它限制了结果集的大小，当只需要部分数据时，可以减少内存和处理负载。其次，它可用于通过仅检索当前页面所需的行来为大型结果集创建高效的分页。此外，通过限制返回的行数，它在测试大型表上的查询时也很有用。

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

使用 OFFSET-FETCH 实现分页

说到分页，另一个子句——OFFSET-FETCH——可以在 SQL Server 中使用来实现分页，它允许您通过跳过一定数量的行然后获取定义数量的行来检索特定的记录子集。此子句具有以下语法：

SELECT column_names
FROM table_name
ORDER BY column_name
OFFSET number_of_rows_to_skip ROWS
FETCH NEXT number_of_rows_to_return ROWS ONLY;

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

为了说明这一点，假设您需要跳过按 BusinessEntityID 排序的前 10 条记录并返回后 10 条记录，从而有效地获取分页结果中的第 2 页。您的查询将如下所示：

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

您还可以使用OFFSET-FETCH动态页面大小和页码。例如，此动态查询获取第 3 页的记录，假设每页大小为 10 行：

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

这种子句组合非常棒，因为它可以让您精确控制分页 — 这是一种简洁高效的分页处理方法，尤其适用于 Web 应用程序。此外，它遵循 SQL 标准，因此对于来自其他 RDBMS 的开发人员来说，它具有可移植性且易于理解。最重要的是，与其他方法（例如使用子查询）不同，它直接跳过并获取行，而无需复杂的解决方法。

但值得注意的是，对于大型数据集，分页越深（例如，第 1000 页），查询可能会变得越慢，因为 SQL Server 必须跳过更多行。另一个需要考虑的问题是，不返回总行数，因此如果您需要显示分页元数据（例如总页数），则需要额外的查询——来获取总行数。并且不要忘记，使用时必须使用子句；否则，结果是不可预测的。

使用 SET ROWCOUNT 命令

您可以使用SET ROWCOUNTSQL Server 中的命令来限制语句返回的行数SELECT或受UPDATE或影响的行数DELETE。该命令的语法如下：

SET ROWCOUNT { number | 0 }

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

如果将SET ROWCOUNTandSELECT与其他命令（例如ORDER BYand WHERE）一起使用，它们的交互作用会非常强大。在此组合中，WHERE子句首先筛选行，ORDER BY子句对筛选出的行进行排序，然后SET ROWCOUNT限制从排序结果集返回的行数。

下面是一个示例，我们只想检索按字母顺序排序（按职位）且休假时间超过 50 小时的员工的前五条记录，然后重置行数限制，以便将来的查询返回所有匹配的行：

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

SET ROWCOUNT使用数据修改命令（例如UPDATE或）DELETE的工作原理类似。让我们考虑一个更新数据的示例。运行此脚本将仅将具有 Stocker 职位的第一位员工的职位更改为 Chief Stocker，然后重置行数限制，并显示结果：

USE AdventureWorks2022;
SET ROWCOUNT 1;
UPDATE HumanResources.Employee
SET JobTitle = 'Chief Stocker'
WHERE JobTitle = 'Stocker';
SET ROWCOUNT 0;
-- To see the result of the update
SELECT *
FROM HumanResources.Employee
WHERE JobTitle LIKE ('%Stocker%')
ORDER BY JobTitle;

如您所见，SET ROWCOUNT提供了一种简单的方法来限制结果，而无需复杂的语法。与TOP有时需要子查询来实现更复杂的逻辑的命令不同，它SET ROWCOUNT可以直接与语句一起使用SELECT。但是，该SET ROWCOUNT命令被认为已弃用，因为它限制了结果集，而倾向于TOP使用提供更清晰语义的子句。

缺点是，的效果SET ROWCOUNT是特定于会话的，这意味着必须在每个需要它的会话中设置它。此外，SET ROWCOUNT如果不小心使用，在更复杂的查询中使用可能会导致意想不到的结果。

请注意SET ROWCOUNT，现在越来越少使用了。SQL 标准已经发展，现代 SQL 实践更倾向于对结果集和数据操作进行更明确的控制。因此，Microsoft 建议改用子句TOP，因为SET ROWCOUNT不会影响SQL Server 2022 版本之后的未来版本中的DELETE和UPDATE语句。

使用 dbForge Studio 亲自尝试

我们将在 dbForge Studio for SQL Server 中尝试上述用例之一。

让我们首先检查 AdventureWorks2022 数据库的 HumanResources.Employee 表中有多少名员工担任营销专家职位。我们按如下方式执行此操作：

SELECTCOUNT(*) AS Count
FROM HumanResources.Employee
WHERE JobTitle = 'Marketing Specialist';

我们可以看到，共有五个：

我们想知道哪三位专家的未使用休假时间最多。我们使用以下查询：

SET ROWCOUNT 3;
SELECT*
FROM HumanResources.Employee
WHERE JobTitle = 'Marketing Specialist'
ORDER BY VacationHours DESC;
SET ROWCOUNT 0;

dbForge Studio 返回结果（为了演示的目的，我们改变了列的顺序）：

为什么选择dbForge Studio for SQL Server？

我们相信，dbForge Studio 凭借其增强的用户界面和高级功能，比 SQL Server Management Studio (SSMS) 更出色。虽然 SSMS 围绕基础功能展开，但 dbForge Studio 提供了复杂的工具，如可视化查询构建、数据库比较和同步、数据聚合和分析、自动单元测试以及与版本控制系统的集成。这种对用户体验和强大功能的关注使 dbForge Studio 成为SQL Server 管理中SSMS 的有力替代方案。

总而言之，让我们简单比较一下LIMIT上面讨论的子句的 SQL Server 替代方案：

SELECT TOP：最适合从结果集中快速检索指定数量的行，尤其是当您需要固定限制时。非常适合只需要前 N 条记录的报告或仪表板。
OFFSET-FETCH：最适合大型数据集的分页。它允许您跳过指定数量的行并返回定义的集合，非常适合跨多页显示结果。
SET ROWCOUNT：用于限制旧版 SQL Server 中的行数或更新或删除特定数量的记录。但是，它的使用正在减少，取而代之的是TOP和OFFSET-FETCH。

因此，根据使用情况，每种方法都有其独特的优势。