Set nocount on | off

It is generally written in the storage State policy and the beginning and end. This way, you can reduce network data transmission without returning the affected number of rows.

Can be enhancedProgramThis is a bit more efficient.

Example:

Alter proc [DBO]. [usp_vo_getriskruleanditem]
As
/*
Page: Change Risk Rule Configuration
Action: Update change rule weight score
Created by: deitan. Wang
Created Date: 06.27 11
Test:
Exec usp_vo_getriskruleanditem
*/
Begin
Set nocount on -- start to set the switch and do not return
--- Your code

Set nocount off -- switch off
End

Introduction:

The returned results do not contain information about the number of rows affected by the transact-SQL statement.

Syntax

Set nocount {on | off}

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.

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 perform a query,

At the end of a Transact-SQL statement (such as select, insert, update, and 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,

Therefore, the performance can be significantly improved.

Set nocount is set during execution or runtime, rather than during analysis.

Permission

The Set nocount permission is granted to all users by default.

Example

In the following exampleOsqlWhen executed in a utility or SQL Server Query analyzer, information about the affected number of rows is displayed.

 
Use pubs go -- display the Count message. Select au_lname from authors go use pubs go
 

 
-- Set nocount to on and no longer display the Count message. Set nocount on go select au_lname from authors go
 

 
-- Reset set nocount to off. Set nocount off goFrom: sqlserver online book 
 

If there are multiple statements in the stored procedure, SQL server sends a message to the client application when each statement is completed by default,
 

 
Details the number of rows affected by each statement. Most applications do not need these messages. If you are sure the application does not need them, you can disable these messages,
 

 
To improve the performance of the slow network. We can use the set nocount session to disable these messages for the application. (This value is not required for most applications)
Note that:
1. Stored Procedure:
Like create table, record set is returned, and even the insert into statement is returned. However, this record set has a special record set.
 

 
(This record set cannot be operated without a field). There is a big difference between oledb and ODBC. When a record set is retrieved from ODBC,
 

 
ODBC filters out the special set of records called above (the set of records that occupy only the location but cannot perform any operation-mostly generated by create table or insert)
 

 
While oledb does not filter the special record set when it is used to retrieve the record set from oledb. Therefore, when a stored procedure is used to return a record set,
 

 
If you do not want to return a record, use set nocount on to prohibit the stored procedure from returning the record set. Otherwise, this may cause many detours.
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.
 

 
To prevent returning any results from the trigger, do not define a SELECT statement or assign values to variables in the trigger. If you must assign values to variables in the trigger
 

The Set nocount on statement should be used at the beginning of the trigger 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, multiple records won't work,
 

 
Tip: Key column information SI insufficient or incorrect. Too route rows were affected by update.