SET NOCOUNT (Transact-SQL)
Запрещает вывод количества строк, на которые влияет инструкция Transact-SQL или хранимая процедура, в составе результирующего набора.
Синтаксические обозначения в Transact-SQL
Синтаксис
Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.
Remarks
Если значение инструкции SET NOCOUNT равно ON, то количество строк не возвращается. Если значение инструкции SET NOCOUNT равно OFF, то количество строк возвращается.
Функция @@ROWCOUNT обновляется, даже если значение SET NOCOUNT равно ON.
Инструкция SET NOCOUNT ON запрещает всем инструкциям хранимой процедуры отправлять клиенту сообщения DONEINPROC. Для хранимых процедур с несколькими инструкциями, не возвращающих большое количество фактических данных, или для процедур, содержащих циклы Transact-SQL, установка в инструкции SET NOCOUNT параметра ON может значительно повысить производительность за счет существенного снижения объема сетевого трафика.
Инструкция SET NOCOUNT устанавливается во время выполнения, а не на этапе синтаксического анализа.
Чтобы просмотреть текущее значение для этого параметра, выполните следующий запрос.
Разрешения
Необходимо быть членом роли public.
Примеры
В следующем примере запрещается вывод сообщения о количестве измененных строк. В следующем примере сообщение «(5 rows affected)» будет возвращаться клиентам только из первой инструкции SELECT.
SET NOCOUNT (Transact-SQL)
Stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set.
Transact-SQL Syntax Conventions
Syntax
To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.
Remarks
When SET NOCOUNT is ON, the count is not returned. When SET NOCOUNT is OFF, the count is returned.
The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.
SET NOCOUNT ON prevents the sending of DONEINPROC messages to the client for each statement in a stored procedure. For stored procedures that contain several statements that do not return much actual data, or for procedures that contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.
The setting specified by SET NOCOUNT is in effect at execute or run time and not at parse time.
To view the current setting for this setting, run the following query.
Permissions
Requires membership in the public role.
Examples
The following example prevents the message about the number of rows affected from being displayed. In the following example, «(5 rows affected)» will only be returned to clients from the first SELECT statement.
Оптимизация хранимых процедур в 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. Используй имя схемы с именем объекта: Ну тут думаю понятно. Данная операция подсказывает серверу где искать объекты и вместо того чтобы беспорядочно шарится по своим закромам, он сразу будет знать куда ему нужно пойти и что взять. При большом колличестве баз, таблиц и хранимых процедур может значительно сэкономить наше время и нервы.
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
Заключение
В принципе на сегодня у меня всё. Еще раз повторюсь, что здесь лишь те приёмы, которые использовал лично я в своей практике, и могу ручаться за их эффективность.
@@OPTIONS (Transact-SQL)
Возвращает сведения о текущих параметрах инструкции SET.
Синтаксические обозначения в Transact-SQL
Синтаксис
Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.
Типы возвращаемых данных
integer
Комментарии
Параметры могут быть получены при использовании команды SET или из значения sp_configure user options. Значения сеанса, настроенные командой SET, переопределяют параметры sp_configure. Многие средства (такие как Среда Management Studio автоматически настраивают набор параметров. Каждый пользователь имеет функцию @@OPTIONS, представляющую конфигурацию.
Вы можете изменить язык и параметры обработки запроса для определенного сеанса пользователя, используя выражение SET. @@OPTIONS обнаруживает только параметры, для которых заданы значения ON или OFF.
Функция @@OPTIONS возвращает битовую матрицу параметров, преобразованную в десятичное целое число. Битовые параметры хранятся в местах, описанных в таблице в статье Настройка параметра конфигурации сервера user options.
Примеры
A. Пример того, как изменения влияют на поведение
В приведенном ниже примере демонстрируется разница в том, как производится объединение при двух разных настройках параметра CONCAT_NULL_YIELDS_NULL.
Б. Проверка настройки NOCOUNT клиента
SQLShack
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.
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.
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.
