How to use SET NOCOUNT ON in SQL Server

Sign in to answer this question.

• GV

  geetha velusamy4y agoPosted Nov 12, 2021, 1:03 PM

  SET NO COUNT ON:

  SET NO COUNT is ON the count is not returned.

  Sample Query for SET NO COUNT ON:

  SET NOCOUNT ON;  
  GO  
  SELECT [empid]
        ,[empname]
        ,[email]
        ,[address]
  FROM [emp] 

   

  SET NO COUNT OFF:

  SET NO COUNT is OFF it returns the count of rows affected.

  Sample Query for SET NO COUNT OFF:

  SET NOCOUNT OFF;  
  GO  
  SELECT [empid]
        ,[empname]
        ,[email]
        ,[address]
  FROM [emp] 

  

   
• VT

  Vinitha T4y agoPosted Nov 8, 2021, 1:56 PM

  SET NOCOUNT ON - This prevent to display number of row(s) affected and will display as Commands completed successfully in message tab

   

  SET NOCOUNT OFF - This display number of row(s) affected.

   
• 

  Mageshwaran R6y agoPosted Nov 5, 2019, 8:58 AM

  Hi,

  Try this code:

   

  SET NOCOUNT ON -(Auto generate column off) 

   

  SET NOCOUNT OFF-(Auto generate column on) 
• SA

  Subbarao A7y agoPosted Jul 10, 2019, 3:22 AM

  Hi 

   

         SET NOCOUNT ON will tell SQL database engine do not return the count of rows effected in the query.

   

   Try simple query to your tables 

   

  select * from yourtable 

   

  check the Messages tab in the query result window. Now try with NOCOUNT ON

   

  SET NOCOUNT ON 

   select * from yourtable

   

  check the Messages tab in the query result window

   

   

   
• 

  Sivakumar Koneti7y agoPosted Jul 9, 2019, 12:48 AM

  Read once

   

  https://stackoverflow.com/questions/1483732/set-nocount-on-usage
• 

  Rajanikant Hawaldar7y agoPosted Jul 8, 2019, 3:42 AM

  Hi Chittaranjan,

   

  Sometimes even the simplest things can make a difference. One of these simple items that should be part of every stored procedure is SET NOCOUNT ON. This one line of code, put at the top of a stored procedure turns off the messages that SQL Server sends back to the client after each T-SQL statement is executed. This is performed for all SELECT, INSERT, UPDATE, and DELETE statements. Having this information is handy when you run a T-SQL statement in a query window, but when stored procedures are run there is no need for this information to be passed back to the client.

  By removing this extra overhead from the network it can greatly improve overall performance for your database and application.

  If you still need to get the number of rows affected by the T-SQL statement that is executing you can still use the @@ROWCOUNT option. By issuing a SET NOCOUNT ON this function (@@ROWCOUNT) still works and can still be used in your stored procedures to identify how many rows were affected by the statement.

  Microsoft even realized the issue that this creates and has changed the stored procedure templates from SQL Server 2000 to SQL Server 2005.

  Here is the old template style available in SQL Server 2000 without the SET NOCOUNT ON.

  1. -- =============================================  
  2. -- Create procedure basic template  
  3. -- =============================================  
  4. -- creating the store procedure  
  5. IF EXISTS (SELECT name   
  6. FROM sysobjects  
  7. WHERE name = N' '    
  8. AND type = 'P')  
  9. DROP PROCEDURE    
  10. GO  
  11.   
  12. CREATE PROCEDURE     
  13. <@param1, sysname, @p1> int> = ,   
  14. <@param2, sysname, @p2> int> =   
  15. AS  
  16. SELECT @p1, @p2  
  17. GO  
  18.   
  19. -- =============================================  
  20. -- example to execute the store procedure  
  21. -- =============================================  
  22. EXECUTE   ,   
  23. GO  

  Here is the new template style available in SQL Server 2005 with the SET NOCOUNT ON. 

  1. -- ================================================  
  2. -- Template generated from Template Explorer using:  
  3. -- Create Procedure (New Menu).SQL  
  4. --  
  5. -- Use the Specify Values for Template Parameters  
  6. -- command (Ctrl-Shift-M) to fill in the parameter  
  7. -- values below.  
  8. --  
  9. -- This block of comments will not be included in  
  10. -- the definition of the procedure.  
  11. -- ================================================  
  12. SET ANSI_NULLS ON  
  13. GO  
  14. SET QUOTED_IDENTIFIER ON  
  15. GO  
  16. -- =============================================  
  17. -- Author:   
  18. -- Create date:   
  19. -- Description:   
  20. -- =============================================  
  21. CREATE PROCEDURE    
  22. -- Add the parameters for the stored procedure here  
  23. <@Param1, sysname, @p1> int> = ,  
  24. <@Param2, sysname, @p2> int> =   
  25. AS  
  26. BEGIN  
  27. -- SET NOCOUNT ON added to prevent extra result sets from  
  28. -- interfering with SELECT statements.  
  29. SET NOCOUNT ON;  
  30.   
  31. -- Insert statements for procedure here  
  32. SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>  
  33. END  
  34. GO  

  As you can see even simple little things such as this can make an overall improvement for your database environment. Stay tuned for other simple tricks and techniques to improve performance.

   

  Thanks 
• 

  Amit Mohanty7y agoPosted Jul 8, 2019, 3:26 AM

  When we SET NOCOUNT is ON, the count (indicating the number of rows affected by a T-SQL statement) is not returned. When SET NOCOUNT is OFF, the count is returned. It is used with any SELECT, INSERT, UPDATE, DELETE statement. SET NOCOUNT ON improves stored procedure performance.

   

  Syntex:

  1. SET NOCOUNT ON   
  2.      
  3. OR     
  4.    
  5. SET NOCOUNT OFF 
     

  For more details refers to below links
  
  https://www.c-sharpcorner.com/blogs/set-nocount-on-and-set-nocount-off-using-stored-procedure-in-sql-server
  
  https://www.tutorialgateway.org/sql-set-nocount-on/
  
  https://www.tech-recipes.com/rx/56506/sql-server-set-nocount-on-statement-with-examples/

   

   

   
• 

  Madan Shekar7y agoPosted Jul 8, 2019, 3:25 AM

  https://www.tech-recipes.com/rx/56506/sql-server-set-nocount-on-statement-with-examples/