Use of SET NOCOUNT in sql server

Introduction:
In my previous article I have explained about  how to Restrict the number of rows affected in SQL Server. In  this article I will explain about the use of in SET NOCOUNT sql server.

Explanation:

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, setting SET NOCOUNT ON can provide a significant performance boost, because network traffic is greatly reduced.
The setting specified by SET NOCOUNT is in effect at execute or run time and not at parse time.

Syntax:

SET NOCOUNT {ON | OFF }

Examples:
The following example prevents the message about the number of rows affected from being displayed.

USEAdventureWorks;
GO
SET NOCOUNT OFF;
GO
— Display the count message.
SELECT TOP(5)LastName
FROM Person.Contact
WHERE LastName LIKE ‘A%’;
GO
— SET NOCOUNT to ON to no longer display the count message.
SET NOCOUNT ON;
GO
SELECT TOP(5) LastName
FROM Person.Contact
WHERE LastName LIKE ‘A%’;
GO
— Reset SET NOCOUNT to OFF
SET NOCOUNT OFF;
GO

Do you like this article. Then share with your friends. Or like our Facebook page. Or post your comments below…

Leave a Reply

Your email address will not be published. Required fields are marked *