Skip to content

Latest commit

 

History

History
99 lines (73 loc) · 3.05 KB

set-nocount-transact-sql.md

File metadata and controls

99 lines (73 loc) · 3.05 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs monikerRange
SET NOCOUNT (Transact-SQL)
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.
WilliamDAssafMSFT
wiassaf
pijocoder, randolphwest
06/07/2024
sql
t-sql
reference
NOCOUNT
SET_NOCOUNT_TSQL
NOCOUNT_TSQL
SET NOCOUNT
NOCOUNT option
number of rows affected by statement
row affected by statements [SQL Server]
counting rows
SET NOCOUNT statement
TSQL
=azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric

SET NOCOUNT (Transact-SQL)

[!INCLUDE sql-asdb-asdbmi-asa-pdw-fabricdw]

Controls whether a message that shows the number of rows affected by a [!INCLUDE tsql] statement or stored procedure is returned after the result set. This message is an extra result set.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

SET NOCOUNT { ON | OFF }

Remarks

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 [!INCLUDE tsql] 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;

Permissions

Requires membership in the public role.

Examples

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

Related content