1

When I write a procedure in SQL Server 2008, it always write SET NOCOUNT ON.

I googled it, and saw that it's used to suppress the xx row were effected message, but why should I do it?

Is it for security reasons?

EDIT: ok, so I understand from the current answer that it's used mostly for performance, and coherence with the count of the client...

So is there a reason not to use it? Like if I want my client to be able to compare his count with mine?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Tomer Amir
  • 1,515
  • 4
  • 27
  • 54
  • 2
    `for procedures that contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced` – Mihai Mar 09 '14 at 12:23
  • 1
    `SET NOCOUNT` does not prohibit you from checking things like `@@ROWCOUNT`. It just prevents the chatty `n row(s) affected.` messages. See [https://sqlblog.org/blogs/aaron_bertrand/archive/2008/10/30/my-stored-procedure-best-practices-checklist.aspx](https://sqlblog.org/blogs/aaron_bertrand/archive/2008/10/30/my-stored-procedure-best-practices-checklist.aspx). – Aaron Bertrand Mar 09 '14 at 18:02

3 Answers3

2

I believe SET NOCOUNT ON is mostly used to avoid passing back to the client a potentially misleading information. In a stored procedure, for example, your batch may contain several different statements with their own count of affected records but you may want to pass back to the client just a single, perhaps completely different number.

Efran Cobisi
  • 6,138
  • 22
  • 22
2

It's not for security, since a rowcount doesn't really divulge much info, especially compared to the data that is in the same payload.

If you call SQL from an application, the "xxx rows" will be returned to the application as a dataset, with network round trips in between before you get the data, which as Mihai says, can have a performance impact.

Bottom line, it won't hurt to add it to your stored procedure, it is common practice, but you are not obligated to.

SqlACID
  • 4,024
  • 20
  • 28
-1

As per MSDN SET NOCOUNT ON

Stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set.

When SET NOCOUNT is ON, the count is not returned. When SET NOCOUNT is OFF, the count is returned. The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.

Another related good post on SO

SET NOCOUNT ON usage

Taken from SET NOCOUNT ON Improves SQL Server Stored Procedure Performance

SET NOCOUNT ON turns off the messages that SQL Server sends back to the client after each T-SQL statement is executed. This is performed for all SELECT, INSERT, UPDATE, and DELETE statements. Having this information is handy when you run a T-SQL statement in a query window, but when stored procedures are run there is no need for this information to be passed back to the client.

By removing this extra overhead from the network it can greatly improve overall performance for your database and application.

If you still need to get the number of rows affected by the T-SQL statement that is executing you can still use the @@ROWCOUNT option. By issuing a SET NOCOUNT ON this function (@@ROWCOUNT) still works and can still be used in your stored procedures to identify how many rows were affected by the statement.

so is there a reason not to use it? Instead use @@ROWCOUNT if you want to compare the count of rows affected.

Community
  • 1
  • 1
Rahul
  • 76,197
  • 13
  • 71
  • 125