SET NOCOUNT Stops the message indicating the number of rows affected by a Transact-SQL statement from being returned as part of the results.
Syntax SET NOCOUNT
Remarks When SET NOCOUNT is ON, the count (indicating the number of rows affected by a Transact-SQL statement) 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 eliminates the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. When using the utilities provided with Microsoft� SQL Server� to execute queries, the results prevent «nn rows affected» from being displayed at the end Transact-SQL statements such as SELECT, INSERT, UPDATE, and DELETE.
For stored procedures that contain several statements that do not return much actual data, this can provide a significant performance boost because network traffic is greatly reduced.
The setting of SET NOCOUNT is set at execute or run time and not at parse time.
� ���-�� � BOL �� ����� �������� ������� �������� SET NOCOUNT
Duration of SET Options This section describes the duration of SET options.
SET options that are set by a user in a script apply until reset or the user’s session with the server is terminated.
SET options that are set within a stored procedure or trigger apply until reset inside that stored procedure or trigger, or until control returns to the code that invoked the stored procedure or trigger.
Unless explicitly reset, SET option values from all higher level code apply within a stored procedure or trigger.
Запрещает вывод количества строк, на которые влияет инструкция Transact-SQL или хранимая процедура, в составе результирующего набора.
Синтаксические обозначения в Transact-SQL
Синтаксис
Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.
Remarks
Если значение инструкции SET NOCOUNT равно ON, то количество строк не возвращается. Если значение инструкции SET NOCOUNT равно OFF, то количество строк возвращается.
Функция @@ROWCOUNT обновляется, даже если значение SET NOCOUNT равно ON.
Инструкция SET NOCOUNT ON запрещает всем инструкциям хранимой процедуры отправлять клиенту сообщения DONE_IN_PROC. Для хранимых процедур с несколькими инструкциями, не возвращающих большое количество фактических данных, или для процедур, содержащих циклы Transact-SQL, установка в инструкции SET NOCOUNT параметра ON может значительно повысить производительность за счет существенного снижения объема сетевого трафика.
Инструкция SET NOCOUNT устанавливается во время выполнения, а не на этапе синтаксического анализа.
Чтобы просмотреть текущее значение для этого параметра, выполните следующий запрос.
Разрешения
Необходимо быть членом роли public.
Примеры
В следующем примере запрещается вывод сообщения о количестве измененных строк.
На прошлой неделе я пересматривал статью и обнаружил, что мне нужна инфомация об использовании NOCOUNT в качестве стандартного средства при написании хранимых процедур. Быстрый поиск в интернете нашел мою старую статью. Видимо, меня спросили, можно ли включить NOCOUNT для конкретной базы данных SQL Server. Как предполагалось в статье, это не было возможным. Опция NOCOUNT устанавливается для всего экземпляра, для конкретного подключения или в коде T-SQL.
Поскольку эта статья была написана задолго до появления новейшего оператора ALTER DATABASE SCOPED CONFIGURATION, я понадеялся, что теперь стало возможным включить NOCOUNT для базы данных. Оказалось, что нет, т.к. перечисленные здесь допустимые опции не включают NOCOUNT. Печально.
Но, конечно, я все же попытался.
На самом деле не удалось.
Я попытался включить NOCOUNT для моего экземпляра SQL 2019, и никак. Вообще.
Провал
Используя код из моей предыдущей статьи, вы можете включить NOCOUNT для экземпляра, установив user option в 512:
Теперь откройте новое окно запроса в SQL Server Management Studio (SSMS), установите вывод результатов в тексте, чтобы проще их просматривать, и выполните запрос. Тогда вы увидите это:
Это не то поведение, которое ожидалось! Моей первой реакцией было, что я что-то не так сделал. Я решил попробовать в Azure Data Studio (ADS), подключившись и выполнив запрос:
Тот же результат. Два инструмента, и результирующий набор показывает число обработанных строк, несмотря на явно установленную пользовательскую опцию.
И графический интерфейс SSMS также подтверждает это:
Поворот
Как бы то ни было, я потратил время, пытаясь выяснить, что происходит. Я могу вручную включить и выключить NOCOUNT внутри T-SQL и увидеть число обработанных строк (или нет). Я проверял и перепроверял все, что приходило мне в голову, и чувствовал себя потерявшим рассудок. Я начал спрашивать себя, как мне удалось стать сертифицированным специалистом в SQL Server.
Я имею в виду, что это простое изменение конфигурации. Это не ракетная техника.
Поэтому я делаю то, что сделал бы любой другой в этой ситуации.
Я выключил свой ноут и выбросил это из головы на несколько дней.
В конце концов я решил снова открыть ноут попытаться опять. Я могу все воспроизвести. Поэтому я обратился к нескольким друзьям, наблюдали ли они также подобные проблемы. Один из них, Karen López (@datachick), задал мне несколько наводящих вопросов. Эти вопросы заставили меня подумать о других способах проверки. Я внезапно понял, что могу проверить опции, установленные в моем текущем подключении:
Выполнение вышеприведенного кода вернуло следующий результат для моего подключения:
И тут меня осенило. Мое подключение не имело включенного NOCOUNT! Я не хочу сказать, что был сильно удивлен, но это помогло увидеть, что отсутствует. Я решил открыть подключение с помощью SQLCMD и посмотреть на поведение по умолчанию для этого подключения. Разумеется, NOCOUNT включен, как и ожидалось:
Моя строка подключения не имела дополнительных опций, и поведение NOCOUNT соблюдается. Это ожидаемое поведение для экземпляра. Теперь мне нужно проверить, что происходит под капотом при подключении к SQL Server с использованием SSMS или ADS. Используя сессию по умолчанию xEvents, я захватил строку подключения, переданную при подключении из ADS, и нашел эту драгоценность:
Пункт конфигурации NOCOUNT в опциях пользователя не распознается при подключении с помощью этих инструментов. Другие опции по-видимому соответствуют, но по неведомым причинам NOCOUNT игнорируется. Это объясняет, почему я наблюдал неожиданное поведение.
Пока оставлю свой сертификат.
Выводы
Я не знаю, баг это или фича. Но это несомненно раздражающее поведение для конечного пользователя, подобного мне. Но если я устанавливаю SET NOCOUNT для SQL server, я ожидаю, что она применяется для всех пользователей, подключающихся с этого момента. Поскольку поведение других опция кажется соответствующим, должно быть что-то не так с NOCOUNT.
Не важно, как подключаются пользователи. SSMS и ADS должны соответствовать настройкам сервера. Я подозреваю, что другие инструменты, вероятно, используют тот же код, что и SSMS и ADS, а это означает, что вам следует дважды проверить фактическую строку подключения, используемую в вашем приложении. Это могло бы объяснить неожиданное поведение.
Обратные ссылки
Нет обратных ссылок
Комментарии
Показывать комментарии Как список | Древовидной структурой
Язык Transact-SQL предоставляет несколько инструкций SET, которые изменяют текущий сеанс, управляя специфическими данными. Инструкции SET группируются в категории, показанные в следующей таблице.
Сведения об установке локальных переменных с помощью инструкции SET см. в разделе SET @local_variable (Transact-SQL).
Категория
Операторы
Инструкции даты и времени
SET DATEFIRST
SET DATEFORMAT
Инструкции блокировки
SET DEADLOCK_PRIORITY
SET QUOTED_IDENTIFIER
Инструкции выполнения запросов
SET ARITHABORT
SET FMTONLY Примечание. В будущей версии Microsoft SQL Server этот компонент будет удален. Избегайте использования этого компонента в новых разработках и запланируйте изменение существующих приложений, в которых он применяется.
SET RESULT SET CACHING (предварительная версия) Примечание. Эта возможность действует только для Azure Synapse Analytics.
SET TEXTSIZE
Инструкции настроек ISO
SET ANSI_DEFAULTS
SET ANSI_WARNINGS
Статистические инструкции
SET FORCEPLAN
SET STATISTICS TIME
Инструкции управления транзакциями
SET IMPLICIT_TRANSACTIONS
Рекомендации по использованию инструкций SET
Все инструкции SET выполняются во время выполнения или запуска, кроме следующих инструкций, которые выполняются во время анализа:
Если инструкция SET выполняется в хранимой процедуре или триггере, значение параметра SET восстанавливается после того, как хранимая процедура или триггер вернет управление. Также, если инструкция SET указана в динамической строке SQL, которая выполняется с помощью процедуры sp_executesql или инструкции EXECUTE, значение параметра SET восстанавливается после того, как управление вернется из пакета, указанного в динамической строке SQL.
Хранимые процедуры выполняются с настройками SET, указанными во время выполнения, кроме инструкций SET ANSI_NULLS и SET QUOTED_IDENTIFIER. Хранимые процедуры, использующие инструкцию SET ANSI_NULLS или SET QUOTED_IDENTIFIER, используют настройку, указанную в хранимой процедуре во время создания. При использовании внутри хранимой процедуры любые установки SET игнорируются.
Параметр user options процедуры sp_configure допускает настройку в пределах сервера и работает с множеством баз данных. Эта настройка ведет себя так же, как и явная инструкция SET, за исключением того, что возникает во время входа в систему.
Настройки базы данных, устанавливаемые с помощью инструкции ALTER DATABASE, действительны только на уровне базы данных и применяются только при явном задании. Параметры базы данных переопределяют параметры экземпляра, которые устанавливаются с помощью процедуры sp_configure.
Если в инструкции SET используется значение ON и OFF, можно указать любое из них для нескольких параметров SET.
Это не относится к статистическим параметрам SET.
Например, инструкция SET QUOTED_IDENTIFIER, ANSI_NULLS ON устанавливает параметры QUOTED_IDENTIFIER и ANSI_NULLS в значение ON.
Настройки инструкции SET переопределяют идентичные настройки параметров базы данных, которые были установлены с помощью инструкции ALTER DATABASE. Например, значение, указанное в инструкции SET ANSI_NULLS, перекроет настройку базы данных для параметра ANSI_NULL. Кроме того, для некоторых настроек подключений автоматически устанавливается значение ON, если пользователь подключается к базе данных, основываясь на значениях, заданных предыдущим использованием настроек процедуры sp_configure user options, или на значениях, которые применимы ко всем подключениям ODBC и OLE/DB.
Настройка SET LOCK_TIMEOUT не влияет на выполнение инструкций ALTER, CREATE и DROP DATABASE.
Если инструкция SET глобальной или быстрой настройки устанавливает несколько настроек, то следующая инструкция SET быстрой настройки переопределяет предыдущие настройки для всех параметров, которых она касается. Если параметр SET, которого касается инструкция SET быстрой настройки, устанавливается после запуска такой инструкции, то индивидуальная инструкция SET переопределяет соответствующие быстрые настройки. Пример инструкции SET быстрой настройки: SET ANSI_DEFAULTS.
При использовании пакетов контекст базы данных определяется пакетом, установленным с помощью инструкции USE. Незапланированные запросы и все другие инструкции, которые выполняются за пределами хранимых процедур и которые содержатся в пакетах, наследуют настройки параметров базы данных и подключения, установленные с помощью инструкции USE.
Запросы режима MARS совместно используют глобальное состояние, которое содержит большую часть настроек параметров SET последней сессии. Параметры SET могут измениться при выполнении любого запроса. Эти изменения специфичны для контекста запроса, в котором они устанавливаются, и не влияют на другие параллельные запросы режима MARS. Однако после завершения выполнения запроса новые параметры SET копируются в глобальное состояние сеанса. Новые запросы, которые выполняются в том же самом сеансе, после этого изменения будут использовать новые значения параметров SET.
При выполнении хранимой процедуры из пакета либо из другой хранимой процедуры она выполняется под значениями параметров, которые установлены в базе данных, содержащей хранимую процедуру. Например, если хранимая процедура db1.dbo.sp1 вызывает хранимую процедуру db2.dbo.sp2, то хранимая процедура sp1 выполняется под текущим значением уровня совместимости базы данных db1, а хранимая процедура sp2 — под текущим значением уровня совместимости базы данных db2.
Когда инструкция Transact-SQL ссылается на объекты, которые размещены в многих базах данных, к ней применяется текущий контекст базы данных и текущий контекст подключения. В этом случае, если инструкция языка Transact-SQL находится в пакете, текущим контекстом соединения является база данных, определенная инструкцией USE. Если инструкция языка Transact-SQL находится в хранимой процедуре, то контекстом соединения является база данных, которая содержит хранимую процедуру.
Если вы создаете индексы в вычисляемых столбцах и индексированных представлениях и управляете ими, необходимо установить для следующих параметров SET значение ON: ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_PADDING и ANSI_WARNINGS. Установите для параметра NUMERIC_ROUNDABORT значение OFF.
Если обязательное значение любого из этих параметров не задано, инструкции INSERT, UPDATE, DELETE, DBCC CHECKDB и DBCC CHECKTABLE для индексированных представлений или таблиц с индексами на основе вычисляемых столбцов не смогут быть выполнены. SQL Server сформирует ошибку с указанием всех неправильно заданных параметров. Также SQL Server будет выполнять инструкции SELECT в этих таблицах или индексных представлениях, как будто индексы в вычисляемых столбцах или представлениях не существуют.
Если для SET RESULT_SET_CACHING указать значение ON, будет включена функция кэширования результатов для текущего сеанса клиента. Для RESULT_SET_CACHING нельзя указать значение ON для сеанса, если на уровне базы данных указано значение OFF. Если для SET RESULT_SET_CACHING указать значение OFF, будет отключена функция кэширования результатов для текущего сеанса клиента. Для изменения этого параметра требуется членство в роли public. Область применения: Azure Synapse Analytics 2-го поколения
Re: SET NOCOUNT ON [new]
Синтаксические обозначения в Transact-SQL
