Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Warehouse in Microsoft Fabric
Controls whether a message that shows the number of rows affected by a Transact-SQL statement or stored procedure is returned after the result set. This message is an extra result set.
Transact-SQL syntax conventions
SET NOCOUNT { ON | OFF }
When SET NOCOUNT
is ON
, the count isn't 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 DONEINPROC
messages to the client for each statement in a stored procedure. For stored procedures that contain several statements that don't return much actual data, or for procedures that contain Transact-SQL loops, setting SET NOCOUNT
to 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.
To view the current setting for this setting, run the following query.
DECLARE @NOCOUNT VARCHAR(3) = 'OFF';
IF ((512 & @@OPTIONS) = 512)
SET @NOCOUNT = 'ON';
SELECT @NOCOUNT AS NOCOUNT;
Requires membership in the public role.
The following example prevents the message about the number of rows affected from being displayed. In the following example, (5 rows affected)
is only returned to clients from the first SELECT
statement.
USE AdventureWorks2022;
GO
SET NOCOUNT OFF;
GO
-- Display the count message.
SELECT TOP (5) LastName
FROM Person.Person
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.Person
WHERE LastName LIKE 'A%';
GO
-- Reset SET NOCOUNT to OFF
SET NOCOUNT OFF;
GO
Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register today