---
title: "What is the use of SET NOCOUNT ON/OFF statement in SQL Server?"  
description: "What is the use of SET NOCOUNT ON/OFF statement in SQL Server?"  
author: "Ashutosh Kumar Verma"  
published: 2021-11-10  
updated: 2021-11-10  
canonical: https://www.mindstick.com/forum/156818/what-is-the-use-of-set-nocount-on-off-statement-in-sql-server  
category: "mssql server"  
tags: ["sql server", "sql"]  
reading_time: 2 minutes  

---

# What is the use of SET NOCOUNT ON/OFF statement in SQL Server?

What is the use of SET NOCOUNT ON/OFF [statement in SQL](https://www.mindstick.com/forum/33706/what-is-the-difference-between-having-clause-and-group-by-statement-in-sql-server) [Server](https://www.mindstick.com/articles/43769/what-is-serverless-architecture-is-it-worth-switching-over)?

## Replies

### Reply by Steilla Mitchel

**SET NOCOUNT ON/OFF in [SQL Server](https://www.mindstick.com/articles/12999/what-is-table-valued-function-in-sql-server):**

SET NOCOUNT is used to count the row that are affected by using the DML statement that is select, insert, delete and update. By default the SET NOCOUNT is OFF in SQL server that are show the number of affected rows. If you don’t want to show the affected row then you can use the SET NOCOUNT ON, then the number of affected rows will not appear.

Example- Following is a SQL statement that have no use of SET NOCOUNT ON or OFF but by default SET NOCOUNT is OFF in SQL server. You can use SET NOCOUNT OFF instead of none.

![What is the use of SET NOCOUNT ON/OFF statement in SQL Server?](https://www.mindstick.com/mindstickforums/9ea14eed-8648-4902-92bf-ce331710f17e/images/1b1ec3c2-41e6-4d46-a1ab-cba61f9d68d7.png)

![What is the use of SET NOCOUNT ON/OFF statement in SQL Server?](https://www.mindstick.com/mindstickforums/9ea14eed-8648-4902-92bf-ce331710f17e/images/4ee82220-5922-478a-add9-a8a6ff18dbfe.png)

![What is the use of SET NOCOUNT ON/OFF statement in SQL Server?](https://www.mindstick.com/mindstickforums/9ea14eed-8648-4902-92bf-ce331710f17e/images/fd25780e-b8f3-4156-ac9c-724c6f67b327.png)

Now, the following SQL statement is execute with using of SET NOCOUNT ON,

![What is the use of SET NOCOUNT ON/OFF statement in SQL Server?](https://www.mindstick.com/mindstickforums/9ea14eed-8648-4902-92bf-ce331710f17e/images/4955ce59-937a-477f-871b-f4d9206bb439.png)

![What is the use of SET NOCOUNT ON/OFF statement in SQL Server?](https://www.mindstick.com/mindstickforums/9ea14eed-8648-4902-92bf-ce331710f17e/images/1950368b-526d-4bf5-8159-89a3efb3c7b2.png)\


---

Original Source: https://www.mindstick.com/forum/156818/what-is-the-use-of-set-nocount-on-off-statement-in-sql-server

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.