August 17, 2016 at 10:25 am
The following command and comment is included by default in new sproc templates genned via SSMS:
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
I wrote an update sproc and I wanted to return the number of rows affected. I was able to do this by returning @@rowcount but I had to "set nocount on". This works as expected but I'm concerned that I'm circumventing a default guard that was added by the ssms team.
Can you provide any insight into what is meant by the comment added above "set nocount off" and if there are any caveats I should be aware of when setting "set nocount on", any particular scenarios where I should not "set nocount on" or if there is an alternate approach I should use to return rows affected?
August 17, 2016 at 10:28 am
As far as I know, there's two main reasons to set NOCOUNT to ON:
1) The rowcount for each individual query in a stored procedure is rarely relevant, so small amounts of resources are wasted outputting something which is never really used. As you mentioned, when it is relevant, the stored proc generally would contain the @@rowcount clause to obtain the result.
2) When used in external applications, like a .NET website or application, I'm fairly sure the rowcount actually constitutes a separate recordset, and so this might cause unexpected behaviour if not handled properly by the application.
-- also upon re-reading, it's odd that your SSMS sets NOCOUNT to OFF ... mine defaults to ON, which is more sensible
August 17, 2016 at 1:10 pm
The SET NOCOUNT ON tells SQL Server to not print out the number of rows affected by each query. When connecting via external applications, I've seen it shown that this (and the PRINT statement) actually sends bytes to the calling application, which can interfere with the timeout on the connection. I haven't done any testing to see if it impacts execution time or not.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply