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.

USE AdventureWorks;

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…