Use of SET NOCOUNT in sql server

By
Advertisement

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…


0 comments:

Post a Comment

Online Casino