使用SET ROWCOUNT是否安全?

时间:2021-05-23 16:27:11

I am using SET ROWCOUNT because the value comes from a parameter into my procedure.

我正在使用SET ROWCOUNT,因为值来自我的过程中的参数。

SET ROWCOUNT @take 

SELECT * FROM Something

SET ROWCOUNT 0

Is it possible to another procedure executes at the same time and get the rowcount setting, or is it perfectly safe to use it on a stored procedure?

是否可以同时执行另一个过程并获取rowcount设置,或者在存储过程中使用它是否完全安全?

3 个解决方案

#1


7  

Rowcount is specific to your current scope, so you are safe there. However, Books Online tells me this (which may or may not affect your needs):

Rowcount特定于您当前的范围,因此您在那里安全。但是,联机丛书告诉我这(可能会或可能不会影响您的需求):

Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server. Do not use SET ROWCOUNT with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. Also, for DELETE, INSERT, and UPDATE statements that currently use SET ROWCOUNT, we recommend that you rewrite them to use the TOP syntax. For more information, see DELETE (Transact-SQL), INSERT (Transact-SQL), or UPDATE (Transact-SQL).

使用SET ROWCOUNT不会影响SQL Server下一版本中的DELETE,INSERT和UPDATE语句。不要在新的开发工作中将SET ROWCOUNT与DELETE,INSERT和UPDATE语句一起使用,并计划修改当前使用它的应用程序。此外,对于当前使用SET ROWCOUNT的DELETE,INSERT和UPDATE语句,我们建议您重写它们以使用TOP语法。有关更多信息,请参阅DELETE(Transact-SQL),INSERT(Transact-SQL)或UPDATE(Transact-SQL)。

TOP can use variables too and now can be used in INSERT,UPDATE and DELETE statments. (Hey I learned something new today.) Look up how to use TOP with variables in Books online.

TOP也可以使用变量,现在可以在INSERT,UPDATE和DELETE语句中使用。 (嘿,我今天学到了一些新知识。)查看如何在联机丛书中使用TOP和变量。

#2


5  

I am adding this answer for the benefit of people who may still search for this.

我正在添加此答案,以便为可能仍在搜索此问题的人员提供帮助。

It is no longer safe to use SET ROWCOUNT as it will be deprecated in the next version of SQL Server:

使用SET ROWCOUNT不再安全,因为它将在下一版本的SQL Server中弃用:

TechNet: Deprecated Database Engine Features

TechNet:不推荐使用的数据库引擎功能

#3


0  

I am using it like this FOR SELECT ONLY SO ITS NOT going to be deprecated yet as a monitor thread i need to select all pending rows from this one table but, as the process thread i only want to process 10 rows at a time but i don't want spaghetti code in my sproc so here is my solution:

我正在使用它像这样FOR SELECT ON它不会被弃用作为监视器线程我需要从这一个表中选择所有挂起的行但是,作为进程线程我只想一次处理10行但是我不要在我的sproc中使用意大利面条代码,所以这是我的解决方案:

parameters ( @top int = 0 )

参数(@top int = 0)

IF @TOP > 0
    SET ROWCOUNT @TOP -- only put a cap on the select if we pass in the @top value

-- select as normal select * from aTable -- either gets all rows or gets a virtual "top (@top)" -- example: exec up_myProc @top=10 -- gets top 10 -- exec up_myProc @top=0 -- gets all rows

- 从aTable中选择正常select * - 获取所有行或获取虚拟“top(@top)” - 例如:exec up_myProc @ top = 10 - 获得前10名 - exec up_myProc @ top = 0 - - 获取所有行

#1


7  

Rowcount is specific to your current scope, so you are safe there. However, Books Online tells me this (which may or may not affect your needs):

Rowcount特定于您当前的范围,因此您在那里安全。但是,联机丛书告诉我这(可能会或可能不会影响您的需求):

Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server. Do not use SET ROWCOUNT with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. Also, for DELETE, INSERT, and UPDATE statements that currently use SET ROWCOUNT, we recommend that you rewrite them to use the TOP syntax. For more information, see DELETE (Transact-SQL), INSERT (Transact-SQL), or UPDATE (Transact-SQL).

使用SET ROWCOUNT不会影响SQL Server下一版本中的DELETE,INSERT和UPDATE语句。不要在新的开发工作中将SET ROWCOUNT与DELETE,INSERT和UPDATE语句一起使用,并计划修改当前使用它的应用程序。此外,对于当前使用SET ROWCOUNT的DELETE,INSERT和UPDATE语句,我们建议您重写它们以使用TOP语法。有关更多信息,请参阅DELETE(Transact-SQL),INSERT(Transact-SQL)或UPDATE(Transact-SQL)。

TOP can use variables too and now can be used in INSERT,UPDATE and DELETE statments. (Hey I learned something new today.) Look up how to use TOP with variables in Books online.

TOP也可以使用变量,现在可以在INSERT,UPDATE和DELETE语句中使用。 (嘿,我今天学到了一些新知识。)查看如何在联机丛书中使用TOP和变量。

#2


5  

I am adding this answer for the benefit of people who may still search for this.

我正在添加此答案,以便为可能仍在搜索此问题的人员提供帮助。

It is no longer safe to use SET ROWCOUNT as it will be deprecated in the next version of SQL Server:

使用SET ROWCOUNT不再安全,因为它将在下一版本的SQL Server中弃用:

TechNet: Deprecated Database Engine Features

TechNet:不推荐使用的数据库引擎功能

#3


0  

I am using it like this FOR SELECT ONLY SO ITS NOT going to be deprecated yet as a monitor thread i need to select all pending rows from this one table but, as the process thread i only want to process 10 rows at a time but i don't want spaghetti code in my sproc so here is my solution:

我正在使用它像这样FOR SELECT ON它不会被弃用作为监视器线程我需要从这一个表中选择所有挂起的行但是,作为进程线程我只想一次处理10行但是我不要在我的sproc中使用意大利面条代码,所以这是我的解决方案:

parameters ( @top int = 0 )

参数(@top int = 0)

IF @TOP > 0
    SET ROWCOUNT @TOP -- only put a cap on the select if we pass in the @top value

-- select as normal select * from aTable -- either gets all rows or gets a virtual "top (@top)" -- example: exec up_myProc @top=10 -- gets top 10 -- exec up_myProc @top=0 -- gets all rows

- 从aTable中选择正常select * - 获取所有行或获取虚拟“top(@top)” - 例如:exec up_myProc @ top = 10 - 获得前10名 - exec up_myProc @ top = 0 - - 获取所有行