SET NOCOUNT для SQL Server
Пересказ статьи Thomas LaRock. SET NOCOUNT For 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 также подтверждает это:
Прежде чем двигаться дальше, я хочу отметить, что SET NOCOUNT OFF - одна из тех ужасных фраз, с которыми мы сталкиваемся в сфере технологий, когда наш мозг вынужден дважды думать о том, что мы делаем. Следует уволить того, кто так его назвал. Простое SET ROWRESULTS ON|OFF было бы значительно проще и понятней.
Как бы то ни было, я потратил время, пытаясь выяснить, что происходит. Я могу вручную включить и выключить 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, а это означает, что вам следует дважды проверить фактическую строку подключения, используемую в вашем приложении. Это могло бы объяснить неожиданное поведение.
Но, конечно, я все же попытался.
Но мне не удалось.
На самом деле не удалось.
Я попытался включить NOCOUNT для моего экземпляра SQL 2019, и никак. Вообще.
Позвольте объяснить.
Провал
Используя код из моей предыдущей статьи, вы можете включить NOCOUNT для экземпляра, установив user option в 512:
EXEC sys.sp_configure 'user options','512'
GO
RECONFIGURE
GO
Теперь откройте новое окно запроса в SQL Server Management Studio (SSMS), установите вывод результатов в тексте, чтобы проще их просматривать, и выполните запрос. Тогда вы увидите это:
Это не то поведение, которое ожидалось! Моей первой реакцией было, что я что-то не так сделал. Я решил попробовать в Azure Data Studio (ADS), подключившись и выполнив запрос:
Тот же результат. Два инструмента, и результирующий набор показывает число обработанных строк, несмотря на явно установленную пользовательскую опцию.
И графический интерфейс SSMS также подтверждает это:
Поворот
Прежде чем двигаться дальше, я хочу отметить, что SET NOCOUNT OFF - одна из тех ужасных фраз, с которыми мы сталкиваемся в сфере технологий, когда наш мозг вынужден дважды думать о том, что мы делаем. Следует уволить того, кто так его назвал. Простое SET ROWRESULTS ON|OFF было бы значительно проще и понятней.
Как бы то ни было, я потратил время, пытаясь выяснить, что происходит. Я могу вручную включить и выключить NOCOUNT внутри T-SQL и увидеть число обработанных строк (или нет). Я проверял и перепроверял все, что приходило мне в голову, и чувствовал себя потерявшим рассудок. Я начал спрашивать себя, как мне удалось стать сертифицированным специалистом в SQL Server.
Я имею в виду, что это простое изменение конфигурации. Это не ракетная техника.
Поэтому я делаю то, что сделал бы любой другой в этой ситуации.
Я выключил свой ноут и выбросил это из головы на несколько дней.
Река
В конце концов я решил снова открыть ноут попытаться опять. Я могу все воспроизвести. Поэтому я обратился к нескольким друзьям, наблюдали ли они также подобные проблемы. Один из них, Karen López (@datachick), задал мне несколько наводящих вопросов. Эти вопросы заставили меня подумать о других способах проверки. Я внезапно понял, что могу проверить опции, установленные в моем текущем подключении:
DECLARE @options INT
SELECT @options = @@OPTIONS
PRINT @options
IF ( (1 & @options) = 1 ) PRINT 'DISABLE_DEF_CNST_CHK'
IF ( (2 & @options) = 2 ) PRINT 'IMPLICIT_TRANSACTIONS'
IF ( (4 & @options) = 4 ) PRINT 'CURSOR_CLOSE_ON_COMMIT'
IF ( (8 & @options) = 8 ) PRINT 'ANSI_WARNINGS'
IF ( (16 & @options) = 16 ) PRINT 'ANSI_PADDING'
IF ( (32 & @options) = 32 ) PRINT 'ANSI_NULLS'
IF ( (64 & @options) = 64 ) PRINT 'ARITHABORT'
IF ( (128 & @options) = 128 ) PRINT 'ARITHIGNORE'
IF ( (256 & @options) = 256 ) PRINT 'QUOTED_IDENTIFIER'
IF ( (512 & @options) = 512 ) PRINT 'NOCOUNT'
IF ( (1024 & @options) = 1024 ) PRINT 'ANSI_NULL_DFLT_ON'
IF ( (2048 & @options) = 2048 ) PRINT 'ANSI_NULL_DFLT_OFF'
IF ( (4096 & @options) = 4096 ) PRINT 'CONCAT_NULL_YIELDS_NULL'
IF ( (8192 & @options) = 8192 ) PRINT 'NUMERIC_ROUNDABORT'
IF ( (16384 & @options) = 16384 ) PRINT 'XACT_ABORT'
Выполнение вышеприведенного кода вернуло следующий результат для моего подключения:
И тут меня осенило. Мое подключение не имело включенного NOCOUNT! Я не хочу сказать, что был сильно удивлен, но это помогло увидеть, что отсутствует. Я решил открыть подключение с помощью SQLCMD и посмотреть на поведение по умолчанию для этого подключения. Разумеется, NOCOUNT включен, как и ожидалось:
Моя строка подключения не имела дополнительных опций, и поведение NOCOUNT соблюдается. Это ожидаемое поведение для экземпляра.
Теперь мне нужно проверить, что происходит под капотом при подключении к SQL Server с использованием SSMS или ADS. Используя сессию по умолчанию xEvents, я захватил строку подключения, переданную при подключении из ADS, и нашел эту драгоценность:
Пункт конфигурации NOCOUNT в опциях пользователя не распознается при подключении с помощью этих инструментов. Другие опции по-видимому соответствуют, но по неведомым причинам NOCOUNT игнорируется. Это объясняет, почему я наблюдал неожиданное поведение.
Пока оставлю свой сертификат.
Выводы
Я не знаю, баг это или фича. Но это несомненно раздражающее поведение для конечного пользователя, подобного мне. Но если я устанавливаю SET NOCOUNT для SQL server, я ожидаю, что она применяется для всех пользователей, подключающихся с этого момента. Поскольку поведение других опция кажется соответствующим, должно быть что-то не так с NOCOUNT.
Не важно, как подключаются пользователи. SSMS и ADS должны соответствовать настройкам сервера. Я подозреваю, что другие инструменты, вероятно, используют тот же код, что и SSMS и ADS, а это означает, что вам следует дважды проверить фактическую строку подключения, используемую в вашем приложении. Это могло бы объяснить неожиданное поведение.
Обратные ссылки
Автор не разрешил комментировать эту запись
Комментарии
Показывать комментарии Как список | Древовидной структурой