RSS

the use of “SET NOCOUNT ON;” in SQL Server?

06 May

This statement is used to stop the message that shows the count of the number of rows affected by the SQL statement written in the stored procedure or directly SQL Statement

When it is ON - the number of affected rows will not be returned 
When it is OFF - the number of affected rows will be returned

Example 


USE AdventureWorks2012;
GO
SET NOCOUNT OFF;
GO
-- Display the count message. The number of affected rows will be returned 
SELECT TOP(5)LastName
FROM Person.Person
WHERE LastName LIKE 'A%';
GO
-- Disable the Count massage Then Number of affected rows will not be returned.
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

Thanks for All

Eng. Mustafa Elmasry

MSSQL DBA



		
 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s