Usage of SET NOCOUNT ON in SQL Server

• When SET NOCOUNT is ON, the count (indicating the number of rows affected by a Transact-SQL statement) is not returned. When SET NOCOUNT is OFF, the count is returned. It is used with any SELECT, INSERT, UPDATE, DELETE statement.
• The setting of SET NOCOUNT is set at execute or run time and not at parse time.
• SET NOCOUNT ON improves stored procedure (SP) performance.
• Syntax: SET NOCOUNT { ON | OFF }

Example of SET NOCOUNT ON:

Example of SET NOCOUNT OFF: