July 2, 2012 at 2:59 am
Hi Guys...
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?
July 2, 2012 at 3:50 am
SELECT TOP (@take) FROM Something ORDER BY SomeColumn
No need for RowCount, and note that without an order by there's no guarantee which records you get.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 2, 2012 at 3:51 am
Sagar Sawant (7/2/2012)
Hi Guys...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?
Hi Sagar,
The scope of ROWCOUNT is localized like temp tables and another proc executes parallaly cann't get the setting.
However it is advisable to use TOP instead of ROWCOUNT wherever possible.
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
July 4, 2012 at 7:14 am
Also to note from MS:
Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server. Avoid using SET ROWCOUNT with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. For a similar behavior, use the TOP syntax. For more information, see TOP (Transact-SQL).
...
and
Setting the SET ROWCOUNT option causes most Transact-SQL statements to stop processing when they have been affected by the specified number of rows. This includes triggers and data modification statements such as INSERT, UPDATE, and DELETE. 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 and primarily with the SELECT statement.
You can see what devastating effect it can make for INSERT, UPDATE and DELETE if there are some triggers around...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply