Set nocount on and set nocount off

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.
Even if set nocount is on, the @ rowcount function is updated.
When
When set nocount is on, the done_in_proc information of each statement in the stored procedure is not sent to the client. When Microsoft?
SQL Server? When the provided utility (QA) executes a query
Delete) "Nn rows 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.
For example
If there are multiple statements in the stored procedure
When each statement is completed, the server sends a message to the client application, detailing the number of lines affected by each statement. Most applications do not need these messages. If you are sure the application does not need him
You can disable these messages to improve the performance of the slow network. We can use
The nocount session is set to disable these messages for the application. (This value is not required for most applications)
Note that:
1. Stored Procedure:
Like create
Table returns the record set and inserts
The into statement also returns the record set. However, this record set has a special record set (no field, and no operation can be performed on this record set ),
There is a big difference between oledb and ODBC. When getting a record from ODBC, ODBC filters out the special set of records called above (the set of records that only occupy the position but cannot perform any operation-multiple
Create Table or insert
But oledb does not filter the special record set when it obtains the record set from oledb. Therefore, when using a stored procedure to return a record set, you should use
Set nocount on prohibits the stored procedure from returning a record set. Otherwise, many detours may occur.
2. Trigger:

We recommend that you do not have the trigger return any results. This is because the special processing of these returned results must be written to each application that allows modification to the trigger table. Do not
Define a SELECT statement or variable assignment in a trigger. If the variable value must be assigned in the trigger, set nocount should be used at the beginning of the trigger.
On statement to avoid returning any result set.

Today, I encountered a result set returned by the trigger, causing an error in deleting the trigger table. If one record can be deleted, but multiple records cannot be deleted, the message "key column information" is displayed.
Si insufficient or incorrect. Too route rows were affected by update.

Http://www.cnblogs.com/xiangxiang/archive/2006/10/07/522600.aspx

By default, the stored procedure returns the number of rows affected by each statement in the process. If you do not need to use this information in an application (not required by most applications), use
Set nocount on
Statement to terminate the action. Based on the number of statements that affect the row contained in the stored procedure, this will delete one or more round-trips between the client and the server. Although this is not a big problem, it can be used for high-traffic applications.
Negative impact on Program Performance

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.

If some statements contained in the stored procedure do not return much actual data, this setting greatly reduces network traffic and significantly improves performance.

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 Microsoft SQL Server is used
When the provided utility executes a query
Delete) at the end of the query, "Nn rows affected" is not displayed in the query results ".

When you count a large amount of data, you can use off when you do not need to view each record in detail.