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?

You can use a variable with select top. `select top (@take) * from Something` — Mikael Eriksson, Jun 03 '11 at 18:05
Rowcount is deprecated in SQL Server 2008. If you are writing new code do not use rowcount but use TOP instead. — RC_Cleland, Jun 03 '11 at 18:09
Could you provide a link that definitively states set rowcount is deprecated? Because http://technet.microsoft.com/en-us/library/ms188774.aspx doesn't say that. — Aheho, Aug 26 '13 at 00:00

score 8 · Answer 1 · answered Mar 25 '14 at 21:36

8

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

answered Mar 25 '14 at 21:36

Ryan Wade

81
1
2

1

Not completely deprecated though. Fomr your link it says 'SET ROWCOUNT for INSERT, UPDATE, and DELETE statements' so SELECTS should still be supported. – Mike Cheel Aug 08 '17 at 15:14

score 8 · Accepted Answer · answered Jun 03 '11 at 18:05

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.

But TOP can't be used in PDW. – Motomotes Dec 04 '20 at 19:35 — Motomotes, Dec 04 '20 at 19:35

score 1 · Answer 3 · answered Jan 05 '15 at 21:40

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:

parameters ( @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

score 0 · Answer 4 · answered Jun 14 '19 at 08:51

Sorry for the Necro, just stumbled upon this on SQLServer 2016 and thought it might be interesting: If you are using a SELECT <x> INTO tabY from tabX, this will also be affected if you have SET ROWCOUNT <> 0, even if this select happens in a procedure that you are calling. So be careful.

Is it safe to use SET ROWCOUNT?

4 Answers4