Set nocount on что это

SET NOCOUNT ON ИСпользование

Запрещает вывод количества строк, на которые влияет инструкция Transact-SQL или хранимая процедура, в составе результирующего набора.

Такая информация очень даже может пригодиться при отладке, но потом, скорее всего, будет не нужна. Прописав SET NOCOUNT ON вы отключите вывод количества строк, тем самым увеличите производительность за счет сокращения объема трафика, особенно если у вас в процедуре есть циклы или процедура содержит несколько выражений сразу, но не возвращает большого количества строк.

Для подсчета строк, если вам это необходимо, лучше использовать @@rowcount. Одна из причин: если вы используете где-то в проекте DataAdapter или Command, то NOCOUNT может все поломать.

Стоит заметить, что если у вас на сервере NOCOUNT по умолчанию имеет одно значение, а в процедуре вы пишите другое, то локальное значение приоритетнее. Значение по умолчанию можно установить в Management Studio (Сервис-Параметры-Выполнение запроса-SQL Server-Дополнительно):

Ну и на англоязычном StackOverflow есть много интересных вопросов и ответов, касающихся NOCOUNT и его поведения, советую ознакомиться.

Оптимизация хранимых процедур в SQL Server

Доброго дня, хабрачеловек. Сегодня я бы хотел обсудить с вами тему хранимых процедур в SQL Server 2000-2005. В последнее время их написание занимало львиную долю моего времени на работе и чего уж тут скрывать – по окончанию работы с этим делом осталось достаточно информации, которой с удовольствием поделюсь с тобой %пользовательимя%.
Знания, которыми я собираюсь поделиться, к сожалению,(или к счастью) не добыты мной эмперически, а являются, в большей степени, вольным переводом некоторых статей из буржуйских интернетов.
Итак, как можно понять из названия речь пойдет об оптимизации. Сразу оговорюсь, что все действия, которые я сейчас буду описывать, действительно дают существенный(некоторые больший, некоторые меньший) прирост производительности.
Данная статья не претендует на полное раскрытие темы оптимизации, скорее это собрание практик, которые я применяю в своей работе и могу ручаться за их эффективность. Поехали!

1. Включай в свои процедуры строку — SET NOCOUNT ON: С каждым DML выражением, SQL server заботливо возвращает нам сообщение содержащее колличество обработанных записей. Данная информация может быть нам полезна во время отладки кода, но после будет совершенно бесполезной. Прописывая SET NOCOUNT ON, мы отключаем эту функцию. Для хранимых процедур содержащих несколько выражений или\и циклы данное действие может дать значительный прирост производительности, потому как колличество трафика будет значительно снижено.

CREATE PROC dbo.ProcName
AS
SET NOCOUNT ON;
—Здесь код процедуры
SELECT column1 FROM dbo.TblTable1
—Перключение SET NOCOUNT в исходное состояние
SET NOCOUNT OFF;
GO

2. Используй имя схемы с именем объекта: Ну тут думаю понятно. Данная операция подсказывает серверу где искать объекты и вместо того чтобы беспорядочно шарится по своим закромам, он сразу будет знать куда ему нужно пойти и что взять. При большом колличестве баз, таблиц и хранимых процедур может значительно сэкономить наше время и нервы.

SELECT * FROM dbo.MyTable —Вот так делать хорошо
— Вместо
SELECT * FROM MyTable —А так делать плохо
—Вызов процедуры
EXEC dbo.MyProc —Опять же хорошо
—Вместо
EXEC MyProc —Плохо!

3. Не используй префикс «sp_» в имени своих хранимых процедур: Если имя нашей процедуры начинается с «sp_», SQL Server в первую очередь будет искать в своей главной базе данных. Дело в том, что данный префикс используется для личных внутренних хранимых процедур сервера. Поэтому его использование может привести к дополнительным расходам и даже неверному результату, если процедура с таким же имененем как у вас будет найдена в его базе.

4. Используй IF EXISTS (SELECT 1) вместо IF EXISTS (SELECT *): Чтобы проверить наличие записи в другой таблице, мы используем выражение IF EXISTS. Данное выражение возвращает true если из внутреннего выражения возвращается хоть одно изначение, не важно «1», все колонки или таблица. Возращаемые данные, в принципе никак не используются. Таким образом для сжатия трафика во время передачи данных логичнее использовать «1», как показано ниже:

IF EXISTS (SELECT 1 FROM sysobjects
WHERE name = ‘MyTable’ AND type = ‘U’)

5. Используй TRY-Catch для отлова ошибок: До 2005 сервера после каждого запроса в процедуре писалось огромное колличество проверок на ошибки. Больше кода всегда потребляет больше ресурсов и больше времени. С 2005 SQL Server’ом появился более правильный и удобный способ решения этой проблемы:

BEGIN TRY
—код
END TRY
BEGIN CATCH
—код отлова ошибки
END CATCH

Заключение
В принципе на сегодня у меня всё. Еще раз повторюсь, что здесь лишь те приёмы, которые использовал лично я в своей практике, и могу ручаться за их эффективность.

What does SET NOCOUNT ON do?

When you’re working with T-SQL, you’ll often see SET NOCOUNT ON at the beginning of stored procedures and triggers.

What SET NCOUNT ON does is prevent the “1 row affected” messages from being returned for every operation.

I’ll demo it by writing a stored procedure in the Stack Overflow database to cast a vote:

When this stored procedure runs, it does two things: insert rows into Votes, and update rows in Users. When it finishes, it returns two messages in SSMS:

However, if I add SET NOCOUNT ON at the top of the stored procedure:

Then those messages go away, and SQL Server just reports that the query completed successfully:

This means less data gets sent from SQL Server, across the network, over to the application.

How much less data? Well, honestly, not a lot. In most cases, this isn’t a heroic performance change because it doesn’t make a meaningful performance difference. However, if you have code that works in a loop, or modifies a lot of different tables, then it starts to matter more.

You might say to yourself, “Well, then, I only need to add it when I’m working in a loop, or when I’m modifying a lot of different tables.” However, you can’t always predict who’s going to call your code, and they might run YOUR code in a loop – and in that case, reducing the message overhead helps.

That’s why it’s a good practice to start every stored procedure with SET NOCOUNT ON. Get in the practice, and it’s one less thing you’ll have to worry about as your workload scales.

SET NOCOUNT ON statement usage and performance benefits in SQL Server

Have you ever noticed SET NOCOUNT ON statement in T-SQL statements or stored procedures in SQL Server? I have seen developers not using this set statement due to not knowing it.

In this article, we will explore why it is a good practice to use SET NOCOUNT ON with T-SQL statements. We will also learn the performance benefit you can get from it.

Introduction

Before we explore this statement, let’s create a sample table in the SQL Server database with the following script.

Let’s insert a few records data in this table using the following script.

Once you execute the command, you get the following message in SSMS. You get ‘1-row affected’ message for each row.

Suppose you insert a million rows in the table and for each record, you get this message. As you can see, it is not a useful message and does not provide relevant information.

Now, let’s select the records from this table.

In the output, we get two tabs Results and Messages.

• Result tab shows the record from the table
• The messages tab gives the number of rows affected message

Let’s create a stored procedure to get the records from the specified table.

Execute this stored procedure, and you get a similar output in the following screenshot as well.

SET NOCOUNT ON/OFF statement controls the behavior in SQL Server to show the number of affected rows in the T-SQL query.

• SET NOCOUNT OFF – By default, SQL Server shows the number of affected rows in the messages pane
• SET NOCOUNT ON – We can specify this set statement at the beginning of the statement. Once we enable it, we do not get the number of affected rows in the output

Let’s try running the previous queries with NOCOUNT statement.

Execute the insert statement after enabling the NOCOUNT

Once we execute the above queries we do not get messages of 1 row(s) affected. It gives the following message.

Execute the Select statement with NOCOUNT ON, and you get the following output

Similar to the insert statement, we did not get the number of rows affected message in the select statement output.

Execute Stored procedure

We cannot directly execute the stored procedure with the SET NOCOUNT ON statement. The above statement does not work on the stored procedure.

We either need to create a new stored procedure or alter the procedure and add the SET NOCOUNT statement as per the following script.

Execute the stored procedure, and we get the required output without the number of affected rows message.

Configure the behavior of NOCOUNT at instance level

The SET NOCOUNT ON works at the session-level. We need to specify it with each session. In the stored procedures, we specify the code itself. Therefore, it does not require specifying explicitly in the session.

We can use the sp_configure configuration option to use it at the instance level. The following query sets the behavior of SET NOCOUNT ON at the instance level.

If we specify the NOCOUNT ON/OFF in the individual session, we can override the behavior configured at the instance level.

SET NOCOUNT and @@ROWCOUNT function

We can use the @@ROWCOUNT function to get the number of affected rows in SQL Server. The NOCOUNT ON function does not have any impact on the @@ROWCOUNT function.

Execute the following query, and we get the number of rows affected with the Insert statement.

SET NOCOUNT ON and the SQL Trigger

Let’s check the impact of the NOCOUNT statement on the SQL Triggers.

The following command inserts two records in the tblEmployeeDemo table.

Create another table to store the record transaction records inserted using the trigger.

Let’s create a SQL INSERT, Update trigger to capture the records for the insert, update values.

Execute the following query to update an existing value in the tblEmployeeDemo table, and it invokes the SQL trigger for inserting record in the Audit_tblEmployee table.

The update statement updates only one record; however, in the following SSMS message, it shows two rows affected.

It might create issues for us if the further code depends upon the number of rows affected message. We get this message due to update record tblEmployeeDemo and insert record in the Audit_tblEmployee table.

We do not want the result ‘ 1 row affected’ for the data insertion in the audit table. We should use the trigger with SET NOCOUNT ON for suppressing this message.

Let’s alter the trigger with the following script:

Rerun the update statement, and we get the expected result and get only the number of affected rows using the update statement.

The Performance impact of NOCOUNT statement

According to Microsoft documentation, using NOCOUNT option can provide a significant performance improvement.

Let’s explore this performance benefit with the following example.

Create two different stored procedures with different NOCOUNT properties.

Create a stored procedure with default behavior ( NOCOUNT OFF)

Create stored procedure with explicit set statement

Execute both the stored procedures with the different number of rows 1000, 10000, 100000 and 1000000. We want to capture client statistics for these executions. In the query window of SSMS, go to Edit and enable the Include Client Statistics.

Let’s compare the client statistics

with SET NOCOUNT OFF

With SET NOCOUNT ON

Let’s put both screenshots of client statistics together to see a difference

We can see a huge difference in the TDS packagers received from the server, Bytes received from the server and the client processing time. The number of the select statement also shows a significant improvement. We did not specify the Select statement in the stored procedure, but still, SQL Server treats SET statement as a select statement with the default NOCOUNT value. We can reduce the network bandwidth with the SET NOCOUNT ON option in the stored procedures or T-SQL statements. It might not improve the query performance drastically, but definitely, it puts an impact on the processing time, reducing the network bandwidth and client processing times.

Conclusion

In this article, we explored the behavior of T-SQL statements and stored procedures using the SET NOCOUNT ON. We should consider this SET option and eliminate unnecessary messages to reduce network traffic and improve performance.

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book «DP-300 Administering Relational Database on Microsoft Azure». I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.