SET ROWCOUNT (Transact-SQL)SET ROWCOUNT (Transact-SQL)

适用对象: 是SQL Server 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 是并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

使 SQL ServerSQL Server 在返回指定的行数之后停止处理查询。Causes SQL ServerSQL Server to stop processing the query after the specified number of rows are returned.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

SET ROWCOUNT { number | @number_var }   

参数Arguments

number | @number_var number | @number_var
在停止特定查询之前要处理的行数(整数)。Is the number, an integer, of rows to be processed before stopping the specific query.

RemarksRemarks

重要

在 SQL Server 的将来版本中,使用 SET ROWCOUNT 将不会影响 DELETE、INSERT 和 UPDATE 语句。Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in a future release of SQL Server. 应避免在新的开发工作中将 SET ROWCOUNT 与 DELETE、INSERT 和 UPDATE 语句一起使用,并计划修改当前使用它的应用程序。Avoid using SET ROWCOUNT with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. 对于类似行为,请使用 TOP 语法。For a similar behavior, use the TOP syntax. 有关详细信息,请参阅 TOP (Transact-SQL)For more information, see TOP (Transact-SQL).

要将此选项设置为 off 以便返回所有的行,请将 SET ROWCOUNT 指定为 0。To set this option off so that all rows are returned, specify SET ROWCOUNT 0.

设置 SET ROWCOUNT 选项将使大多数 Transact-SQLTransact-SQL 语句在受到指定数目的行影响后停止处理。Setting the SET ROWCOUNT option causes most Transact-SQLTransact-SQL statements to stop processing when they have been affected by the specified number of rows. 其中包括触发器。This includes triggers. ROWCOUNT 选项对动态游标无效,但它可以限制键集的行集和不敏感游标;The ROWCOUNT option does not affect dynamic cursors, but it does limit the rowset of keyset and insensitive cursors. 所以应慎用此选项。This option should be used with caution.

如果行数值较小,则 SET ROWCOUNT 将覆盖 SELECT 语句 TOP 关键字。SET ROWCOUNT overrides the SELECT statement TOP keyword if the rowcount is the smaller value.

SET ROWCOUNT 的设置是在执行时或运行时设置,而不是在分析时设置。The setting of SET ROWCOUNT is set at execute or run time and not at parse time.

权限Permissions

要求具有 public 角色的成员身份。Requires membership in the public role.

示例Examples

SET ROWCOUNT 在达到指定的行数后停止处理。SET ROWCOUNT stops processing after the specified number of rows. 请注意,在下面的示例中有超过 500 行满足 Quantity 小于 300 的条件。In the following example, note that over 500 rows meet the criteria of Quantity less than 300. 但是,应用 SET ROWCOUNT 后,您可以看到并未返回所有行。However, after applying SET ROWCOUNT, you can see that not all rows were returned.

USE AdventureWorks2012;  
GO  
SELECT count(*) AS Count  
FROM Production.ProductInventory  
WHERE Quantity < 300;  
GO  

下面是结果集:Here is the result set.

Count 
----------- 
537 

(1 row(s) affected)

现在,将 ROWCOUNT 设置为 4 并返回所有行,以演示仅返回 4 行。Now, set ROWCOUNT to 4 and return all rows to demonstrate that only 4 rows are returned.

SET ROWCOUNT 4;  
SELECT *  
FROM Production.ProductInventory  
WHERE Quantity < 300;  
GO  
  
(4 row(s) affected)

示例:Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW)并行数据仓库Parallel Data WarehouseExamples: Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW) and 并行数据仓库Parallel Data Warehouse

SET ROWCOUNT 在达到指定的行数后停止处理。SET ROWCOUNT stops processing after the specified number of rows. 请注意,在下面的示例中共有 20 行满足 AccountType = 'Assets' 条件。In the following example, note that more than 20 rows meet the criteria of AccountType = 'Assets'. 但是,应用 SET ROWCOUNT 后,您可以看到并未返回所有行。However, after applying SET ROWCOUNT, you can see that not all rows were returned.

-- Uses AdventureWorks  
  
SET ROWCOUNT 5;  
SELECT * FROM [dbo].[DimAccount]  
WHERE AccountType = 'Assets';  

若要返回所有行,请将 ROWCOUNT 设置为 0。To return all rows, set ROWCOUNT to 0.

-- Uses AdventureWorks  
  
SET ROWCOUNT 0;  
SELECT * FROM [dbo].[DimAccount]  
WHERE AccountType = 'Assets';  

另请参阅See Also

SET 语句 (Transact-SQL)SET Statements (Transact-SQL)