Set Nocount on Usage

SET NOCOUNT ON usage

Ok now I've done my research, here is the deal:

In TDS protocol, SET NOCOUNT ON only saves 9-bytes per query while the text "SET NOCOUNT ON" itself is a whopping 14 bytes. I used to think that 123 row(s) affected was returned from server in plain text in a separate network packet but that's not the case. It's in fact a small structure called DONE_IN_PROC embedded in the response. It's not a separate network packet so no roundtrips are wasted.

I think you can stick to default counting behavior almost always without worrying about the performance. There are some cases though, where calculating the number of rows beforehand would impact the performance, such as a forward-only cursor. In that case NOCOUNT might be a necessity. Other than that, there is absolutely no need to follow "use NOCOUNT wherever possible" motto.

Here is a very detailed analysis about insignificance of SET NOCOUNT setting: http://daleburnett.com/2014/01/everything-ever-wanted-know-set-nocount/

What is the advantage of using Set NOCOUNT ON in stored procedure's which are used in ssis packages?

SET NOCOUNT ON prevents the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. For stored procedures that contain several statements that do not return much actual data, or for procedures that contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.

Source BOL: SET NOCOUNT

Is it correct to use SET NOCOUNT ON only in the root stored procedure in case you want to call other stored procedures in your root stored procedure

SET NOCOUNT ON will prevent row count messages from being sent by the stored procedure, or by any nested stored procedures (or dynamic batches). If a nested procedure or batch sets NOCOUNT OFF, row count messages will be sent for the statements in that batch, but the NOCOUNT OFF behavior will be restored when control returns to the outer proc/batch.

Why do we use the SET NOCOUNT ON; along with the SELECT @@ROWCOUNT;?

SET NOCOUNT ON stops the results from being printed on the console (screen).

The use of @@ROWCOUNT captures the row count as a parameter in T-SQL and makes it possible to use it for further processing. For instance, you could have conditional logic to do something if no rows are updated.

SET NOCOUNT ON and reading messages using C# and ADO.NET

Informational messages (like the rows affected count info) are reported in ADO.Net through the SqlConnection.InfoMessage event. Add a delegate to the event and will be invoked whenever the server transmits an informational message (ie. any error message with severity bellow 10).

there is no way to associate informational messages like afffected count info with the source. You're going to have to do it based on knowledge of the logic and understand that the first message refers to the first update, the second message to the second update etc.

Relying on affected rows count in the client is generaly a bad practice. The many issues ORM layers like NHibernate and ADO.Net datasets have when SET NOCOUNT ON is turned on just shows how problematic this practice is.

How to SET NOCOUNT ON in a vb.net

Dim strSql As String = "SET NOCOUNT OFF; select * from table_issue; SET NOCOUNT ON;"

---

Related Topics