Is it safe to use SET ROWCOUNT?

Question

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

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?

Solution 1

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):

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).

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.

Solution 2

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:

TechNet: Deprecated Database Engine Features

Comments

[Mike Cheel]

Not completely deprecated though. Fomr your link it says 'SET ROWCOUNT for INSERT, UPDATE, and DELETE statements' so SELECTS should still be supported.

[Motomotes]

But TOP can't be used in PDW.