Let us take a look at the SQL server nocount. At Bobcares our MSSQL support services we can give you a detailed overview of the Noncount and its usage.

SQL Nocount

The notification that displays the number of rows impacted by a Transact-SQL statement or stored procedure is no longer returned as part of the result set.

syntax :

  
SET NOCOUNT { ON | OFF } 

The count is not returned when SQL server SET NOCOUNT is ON. The count is returned when SET NOCOUNT is set to OFF. Even when SET NOCOUNT is set to ON, the @@ROWCOUNT function is changed.

SET NOCOUNT ON prohibits DONEINPROC messages from setting it to the client for each statement in a stored procedure. Setting SET NOCOUNT to ON can give a considerable speed increase for stored procedures that contain multiple statements that do not return much real data, or for procedures that involve Transact-SQL loops, because network traffic is considerably reduced.

The setting supplied by SQL server SET NOCOUNT takes effect at the time of execution or run, not at the time of parsing. Run the following query to get the current setting for this setting.  

SQL

DECLARE @NOCOUNT VARCHAR(3) = 'OFF';  
IF ( (512 & @@OPTIONS) = 512 ) SET @NOCOUNT = 'ON';  
SELECT @NOCOUNT AS NOCOUNT;  
  

Permissions

Membership in the public role is required for sql server nocount.

Examples

The example below shows the SQL server nocount and how it avoids the notice regarding the number of impacted rows from being displayed. In the following example, just the first SELECT statement will return “(5 rows impacted)” to clients.       

SQL

USE AdventureWorks2012;  
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  

This value indicates that less data is delivered from SQL Server to the application via the network. However, in most circumstances, this is not an effective performance modification since it does not result in a significant performance difference. However, if we have code that runs in a loop or alters a large number of distinct tables, it becomes more important.

[Need assistance with similar queries? We are here to help]

Conclusion

To sum up we have now seen more on sql server nocount with the support of our tech support team.