Question about set nocount on: what does set nocount on mean by set nocount on?

MSDN explanation:

Prevents the returned message in the result set from displaying the row count message affected by the Transact-SQL statement or stored procedure.

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.

Syntax

SET NOCOUNT { ON | OFF }

Note

When set nocount is ON, no count is returned. When set nocount is OFF, return the count.

Even if set nocount is ON, the @ ROWCOUNT function is updated.

When set nocount is ON, the DONE_IN_PROC message of each statement in the stored procedure is not sent to the client. If the Stored Procedure contains statements that do not return much actual data or contain a Transact-SQL loop, the network communication traffic will be greatly reduced, setting set nocount to ON significantly improves performance.

Set nocount specifies that the setting takes effect during execution or running, rather than during analysis.

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.

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 CED.

The setting specified by set nocount is in effect at execute or run time and not at parse time.

Permission

RequiredPublicThe role's membership identity.

Requires membership inPublicRole.

Example

In the following example, messages with affected rows are not displayed.

USE AdventureWorks;GOSET NOCOUNT OFF;GO-- Display the count message.SELECT TOP(5)LastNameFROM Person.ContactWHERE LastName LIKE 'A%';GO-- SET NOCOUNT to ON to no longer display the count message.SET NOCOUNT ON;GOSELECT TOP(5) LastNameFROM Person.ContactWHERE LastName LIKE 'A%';GO-- Reset SET NOCOUNT to OFFSET NOCOUNT OFF;GO

-----------------------------------------

Triggers make it easy to automatically synchronize data between tables and cross-databases. However, things that do not seem obvious at a glance will have a significant impact on their performance. Here I will describe a command that can be used to reduce the impact of some unexpected problems.

This example uses the repeatedly triggered INSERT trigger, especially when the insert into statement is used for a large number of INSERT operations. During statement execution, when the trigger is triggered again and again, the trigger will publish the DONE_IN_PROC message for each INSERT action, which will significantly speed down.

Especially when the trigger is triggered as a result of the scheduled time of the SQL Server Agent, this speed is particularly slow. The SQL Server Agent automatically imposes a delay after each done_in_proc signal to avoid server congestion. If you want to run the same set of commands through the query analyzer, the execution will be much faster because no such delay is imposed. If you run such a query using the query analyzer and view multiple "n rows affected" generated by multiple statements, the query may be executed repeatedly, the trigger is also re-triggered multiple times, which is much more than it actually needs.

To disable the done_in_proc message, you can use the set nocount on command at the beginning of the trigger statement. In most cases, it is unnecessary to count rows in any way. If necessary, you can re-construct the command to trigger the trigger (or the trigger itself), so that all the changes can be completed as a command. If this process still takes a long time, you should think of a new method to stimulate the trigger for the sake of simplicity.

==============================================================

When set nocount is ON, no count is returned (indicating the number of rows affected by the Transact-SQL statement ).
When set nocount is OFF, return count (default value: OFF ). 
Even if set nocount is on, the @ rowcount function is updated.
When set nocount is on, the done_in_proc information of each statement in the stored procedure is not sent to the client. When you use a utility provided by Microsoft SQL server to execute a query) at the end of the query, "Nn row affected" is not displayed in the query results ".
If some statements contained in the stored procedure do not return much actual data, this setting greatly reduces network traffic and significantly improves performance.
Set nocount is set during execution or runtime, rather than during analysis.
Note: When set nocount is ON, no count is returned (indicating the number of rows affected by the Transact-SQL statement ). When set nocount is OFF, return the count. ... Conclusion: we should add set nocount on to the header of the stored procedure, and add set nocount off when exiting the stored procedure to achieve optimization.