Set nocount on sql что это
SQL-Ex blog
Новости сайта «Упражнения SQL», статьи и переводы
SET NOCOUNT для SQL Server
На прошлой неделе я пересматривал статью и обнаружил, что мне нужна инфомация об использовании 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, а это означает, что вам следует дважды проверить фактическую строку подключения, используемую в вашем приложении. Это могло бы объяснить неожиданное поведение.
Обратные ссылки
Нет обратных ссылок
Комментарии
Показывать комментарии Как список | Древовидной структурой
SET NOCOUNT ON ИСпользование
Объясните пожалуйста, как правильно использовать SET NOCOUNT ON/OFF В хранимах процедурах? Заранее спасибо
1 ответ 1
Для начала разберемся, что это вообще за зверь такой, SET NOCOUNT. Согласно MSDN, данная конструкция
Запрещает вывод количества строк, на которые влияет инструкция 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. Используй имя схемы с именем объекта: Ну тут думаю понятно. Данная операция подсказывает серверу где искать объекты и вместо того чтобы беспорядочно шарится по своим закромам, он сразу будет знать куда ему нужно пойти и что взять. При большом колличестве баз, таблиц и хранимых процедур может значительно сэкономить наше время и нервы.
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
Заключение
В принципе на сегодня у меня всё. Еще раз повторюсь, что здесь лишь те приёмы, которые использовал лично я в своей практике, и могу ручаться за их эффективность.
SQL Server. Интересные советы
Рекомендации по установкам для создания объектов баз данных Microsoft SQL Server
Рекомендации по установкам для создания объектов баз данных Microsoft SQL ServerSET NOCOUNT — меньше трафик
Если инструкция принимает значение ON, то количество строк (которые обработаны инструкцией Transact-SQL) не возвращается. Запрещает всем инструкциям хранимой процедуры отправлять клиенту сообщения DONE_IN_PROC. Если запросы выполняются из программы, то в результирующем наборе таких инструкций Transact-SQL как: SELECT, INSERT, UPDATE и DELETE значение: “nn rows affected” (строк обработано: nn) отображаться не будет. Для хранимых процедур с несколькими инструкциями, не возвращающих большое количество строк данных, это может значительно повысить производительность за счет существенного снижения объема сетевого трафика. Инструкция SET NOCOUNT устанавливается во время исполнения, а не на этапе синтаксического анализа.
ON по умолчанию. Идентификаторы можно заключать в двойные кавычки, а литералы должны быть разделены одинарными кавычками. Все строки, разделенные двойными кавычками, рассматриваются как идентификаторы объектов. Если в именах объектов базы данных используются зарезервированные ключевые слова, то параметру SET QUOTED_IDENTIFIER должно быть присвоено значение ON. При создании или изменении индексов в вычисляемых столбцах или индексированных представлениях параметру SET QUOTED_IDENTIFIER должно быть присвоено значение OFF. Драйвер ODBC и поставщик OLE DB для собственного клиента SQL Server при соединении автоматически присваивают параметру QUOTED_IDENTIFIER значение ON. По умолчанию параметр SET QUOTED_IDENTIFIER имеет значение OFF для соединений из приложений DB-Library. Когда создается хранимая процедура, параметры SET QUOTED_IDENTIFIER и SET ANSI_NULLS фиксируются и используются для последующих вызовов этой хранимой процедуры. При выполнении операций внутри хранимой процедуры значение SET QUOTED_IDENTIFIER не меняется. Если параметр SET ANSI_DEFAULTS имеет значение ON, параметр SET QUOTED_IDENTIFIER включается. Параметр SET QUOTED_IDENTIFIER устанавливается во время синтаксического анализа. Настройка на время синтаксического анализа означает, что если инструкция SET присутствует в пакете или хранимой процедуре, она выполняется вне зависимости от того, достигает ли выполнение кода фактически этой точки. Кроме того, инструкция SET выполняется до выполнения любых инструкций.
Стандарт SQL-92 требует, чтобы операторы “=” и “<>” при использовании со значениями NULL всегда возвращали FALSE. SQL Server интерпретирует пустую строку как один пробел или действительно пустую строку в зависимости от настройки уровня совместимости. Директива SET ANSI_NULLS ON влияет только на сравнения, где в качестве одного из операндов используется NULL в виде переменной или литеральной константы. Если оба операнда представляют собой столбцы или составные выражения, эта настройка не влияет на результат сравнения. Для хранимых процедур SQL Server использует значение настройки SET ANSI_NULLS, которое действовало в момент создания процедуры. Значение SET ANSI_NULLS должно быть равно ON при выполнении распределенных запросов. SET ANSI_NULLS также должно быть ON при создании или изменении индексов вычисляемых столбцов или индексированных представлений (это один из семи обязательных для этого параметров директивы SET: ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER и CONCAT_NULL_YIELDS_NULL должны иметь значение ON, а параметр NUMERIC_ROUNDABORT – значение OFF). Драйвер ODBC и поставщик OLE DB собственного клиента SQL Server при соединении автоматически устанавливают параметру ANSI_NULLS значение ON. Для соединений из приложений DB-Library значением по умолчанию для параметра SET ANSI_NULLS является OFF. Установка значения SET ANSI_NULLS происходит во время запуска или выполнения, но не во время синтаксического анализа.
Формирует предупреждающее сообщение, если значения NULL появляются в статистических функциях: SUM, AVG, MAX, MIN, STDEV, STDEVP, VAR, VARP или COUNT. Инструкции INSERT или UPDATE, выполнение которой привело к ошибке деления на ноль или арифметического переполнения, в соответствии со стандартом SQL-92 будут откачены и сформировано сообщение об ошибке. Конечные пробелы игнорируются для символьных столбцов, а конечные значения NULL игнорируются для бинарных столбцов. Значение ANSI_WARNINGS игнорируется при передаче аргументов хранимой процедуре или пользовательской функции, а также при объявлении и настройке переменных в инструкции пакетных заданий. Например, если объявить переменную как char(3), а затем присвоить ей значение длиннее трех символов, данные будут усечены до размера переменной, а инструкция INSERT или UPDATE завершится без ошибок. Параметр SET ANSI_WARNINGS должен иметь значение ON при создании или изменении индексов, основанных на вычисляемых столбцах или индексированных представлениях. Параметр ANSI_WARNINGS должен быть установлен в ON для выполнения распределенных запросов. Драйвер ODBC собственного клиента и поставщик OLE DB для собственного клиента SQL для SQL Server при соединении автоматически устанавливает параметр ANSI_WARNINGS в значение ON. Параметр SET ANSI_WARNINGS устанавливается во время выполнения, а не во время синтаксического анализа. Если значение параметра SET ARITHABORT или SET ARITHIGNORE установлено в OFF, а значение параметра SET ANSI_WARNINGS установлено в ON, то SQL Server возвращает сообщение об ошибке при обнаружении ошибок деления на ноль и переполнения.
Контролирует способ хранения значений, которые короче, чем заданный размер поля, а также способ хранения в полях типов: char, varchar, binary и varbinary таких значений, которые имеют оконечные пробелы. Производитель рекомендует ON. Значение параметра инструкции SET ANSI_PADDING не оказывает влияния на значения типа nchar, nvarchar, ntext, text, image, а также на большие значения, для которых SET ANSI_PADDING всегда ON. Это означает, что оконечные пробелы и нули не отбрасываются. SET ANSI_PADDING ON необходимо при создании или изменении индексов по вычисляемым столбцам или индексированным представлениям. Драйвер ODBC и поставщик OLE DB для собственного клиента SQL Server при соединении автоматически устанавливают параметр ANSI_WARNINGS в значение ON. Значение параметра SET ANSI_PADDING устанавливается во время выполнения или запуска, а не во время синтаксического анализа.
Ошибка в транзакции приведёт к её откату, а не к предупреждению. Если параметры SET ARITHABORT и SET ANSI WARNINGS установлены в ON, ошибка приведёт к завершению запроса. Если SET ARITHABORT ON а SET ANSI WARNINGS OFF, ошибка прервёт пакет. Установка параметра SET ARITHABORT происходит при запуске или во время исполнения, но не во время синтаксического анализа. SET ARITHABORT ON необходимо при создании или изменении индексов по вычисляемым столбцам или индексированным представлениям.
Если произошла ошибка при исполнении инструкции Transact-SQL, транзакция будет откачена целиком. Инструкция SET XACT_ABORT не влияет на компиляцию ошибок (например, синтаксических). Параметр XACT_ABORT должен иметь значение ON для инструкций изменения данных в явных или неявных транзакциях, применяющихся к большинству поставщиков OLE DB, включая SQL Server. Единственным случаем, когда этот параметр не требуется, является поддержка поставщиком вложенных транзакций. Дополнительные сведения см. в разделе Распределенные запросы и распределенные транзакции. Значение параметра XACT_ABORT устанавливается во время выполнения, а не во время синтаксического анализа. Включение этого параметра позволяет не заботиться об обработке ошибок при вставках и изменениях.
При этой установке, сцепление значения NULL со строкой дает в результате NULL. Настройка SET CONCAT_NULL_YIELDS_NULL устанавливается во время выполнения или запуска, но не во время синтаксического анализа. SET CONCAT_NULL_YIELDS_NULL ON необходимо при создании или изменении индексов по вычисляемым столбцам или индексированным представлениям.
Потеря точности не приводят к формированию сообщений об ошибках, а результат округляется с точностью столбца или переменной, в которых будет сохранен. Потеря точности происходит, когда выполняется попытка сохранения значения с фиксированной точностью в столбце или переменной с меньшей точностью. Если параметру SET NUMERIC_ROUNDABORT присвоено значение ON, параметр SET ARITHABORT определяет серьезность формируемой ошибки. В следующей таблице показано влияние этих двух параметров на сообщения об ошибках при потере точности. Значение параметра SET NUMERIC_ROUNDABORT задается на этапе выполнения или запуска, но не на этапе синтаксического анализа. При создании или изменении индексов вычисляемых столбцов или индексированных представлений параметр SET NUMERIC_ROUNDABORT должен принимать значение OFF.
1. Избегите использования звёздочки (*) в SELECT, всегда перечисляйте только необходимые столбцы.
2. В инструкции INSERT всегда указывайте имена столбцов.
3. Всегда присваивайте таблицам (а при необходимости и столбцам) псевдонимы – это позволяет избежать путаницы. При использовании псевдонима столбца обязательно добавляйте ключевое слово AS.
4. При ссылке на объект всегда указывайте схему (владельца).
5. Избегите использования non-SARGable предикатов (“IS NULL”, “<>”, “!=”, “!>”, “! 100 ).
6. Для сокращения числа итераций старайтесь по возможности использовать строчный оператор CASE. Например:
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 запрещает всем инструкциям хранимой процедуры отправлять клиенту сообщения DONE_IN_PROC. Для хранимых процедур с несколькими инструкциями, не возвращающих большое количество фактических данных, или для процедур, содержащих циклы Transact-SQL, установка в инструкции SET NOCOUNT параметра ON может значительно повысить производительность за счет существенного снижения объема сетевого трафика.
Инструкция SET NOCOUNT устанавливается во время выполнения, а не на этапе синтаксического анализа.
Чтобы просмотреть текущее значение для этого параметра, выполните следующий запрос.
Разрешения
Необходимо быть членом роли public.
Примеры
В следующем примере запрещается вывод сообщения о количестве измененных строк.