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…
Comments
Post a Comment