SQL Server :SET NOCOUNT { ON | OFF } Statement

Syntax

SET NOCOUNT { ON | OFF } 

Using the above syntax we can prevent showing affected rows by any TSQL query statement. Using SET NOCOUNT ON message inside the stored procedure we can improve performance by a significant margin. If there limited amount of data transferring SET NOCOUNT ON statement not much affected but if we have loops and large amount of data this statement can be useful

E.g.

CREATE PROCEDURE [dbo].[USP_Xxxxx]

AS

SET NOCOUNT ON

  BEGIN

      BEGIN TRY

          INSERT INTO [Dbo].[Xxxxx]

SET NOCOUNT ON: count row is not returned. Just a successfully completed message

SET NOCOUNT OFF: count is returned

Ref: https://msdn.microsoft.com/en-us/library/ms189837.aspx