Объяснение конструкций ANSI_NULLS / QUOTED_IDENTIFIER / NOCOUNT в Microsoft SQL Server

Настройка SET ANSI_NULLS ON;

Включает поведение ANSI SQL стандартов для работы с NULL.

Как работает:

• Если SET ANSI_NULLS ON, то сравнения с NULL (например, = NULL или <> NULL) всегда возвращают UNKNOWN, а не TRUE или FALSE. Для проверки на NULL необходимо использовать IS NULL или IS NOT NULL.
• Если SET ANSI_NULLS OFF, то можно использовать = NULL или <> NULL, что нарушает стандарт SQL.

Это настройка обязательна для создания объектов (процедур, функций и т.д.), чтобы обеспечить стандартизированное поведение с NULL.
Всегда включается по умолчанию, начиная с определённых версий SQL Server.

Пример:

Настройка SET QUOTED_IDENTIFIER ON;

Определяет, как SQL Server интерпретирует двойные кавычки («).

Как работает:

• Если SET QUOTED_IDENTIFIER ON, текст в двойных кавычках («) интерпретируется как имя объекта (например, название таблицы или столбца), а не как строковый литерал.
• Если SET QUOTED_IDENTIFIER OFF, текст в двойных кавычках интерпретируется как строковый литерал, аналогично одинарным кавычкам (‘).

Применение:

• Обычно включается для совместимости с ANSI SQL, где двойные кавычки используются только для идентификаторов.
• Необходимо для создания объектов, таких как индексы, процедуры и функции.

Пример:

Настройка SET NOCOUNT ON;

Отключает возвращение информации о количестве строк, затронутых запросом.

Как работает:

• Если SET NOCOUNT ON, SQL Server не отображает сообщение о количестве строк, обработанных INSERT, UPDATE, DELETE, SELECT.
• Если SET NOCOUNT OFF, сервер возвращает такое сообщение (например, (3 rows affected)).

Применение:

• Используется для уменьшения сетевого трафика, особенно в хранимых процедурах или скриптах, где возвращаемая информация о строках не нужна (для снижения объема ненужных сообщений).
• Повышает производительность при массовых операциях.

Пример:

CROSS APPLY vs OUTER APPLY

Как работает OUTER APPLY в Microsoft SQL Server?

OUTER APPLY — это оператор, который позволяет выполнять строко-ориентированное соединение таблицы с табличной функцией или подзапросом, где каждая строка основной таблицы используется в качестве входного параметра. Это расширение стандартного SQL Server.

OUTER APPLY возвращает все строки из левой таблицы, включая те, для которых табличная функция или подзапрос возвращает пустой результат.
Если табличная функция или подзапрос для строки не возвращает данных, соответствующие столбцы результирующего набора будут заполнены NULL.

Это поведение аналогично LEFT OUTER JOIN, но используется для табличных функций или коррелированных подзапросов.

Синтаксис:

Пример использования OUTER APPLY

Предположим, есть две таблицы: Employees и Orders. Нужно для каждого сотрудника получить последние три заказа, которые он обработал, даже если у некоторых сотрудников заказов нет.

Employees:

Orders:

Запрос с OUTER APPLY:

Результат:

Запрос возвращает всех сотрудников и их последние три заказа (если есть), упорядоченные по дате, или NULL для сотрудников без заказов.

В чем отличие в работе между OUTER APPLY и LEFT OUTER JOIN?

Основное отличие между OUTER APPLY и LEFT OUTER JOIN заключается в том, как они работают с динамическими наборами данных и коррелированными подзапросами:

Особенность	OUTER APPLY	LEFT OUTER JOIN
Работа с табличными функциями	Может использовать табличные функции или коррелированные подзапросы, зависящие от текущей строки левой таблицы.	Работает только с фиксированными наборами данных, независимыми от текущей строки.
Гибкость условий	Позволяет динамически изменять результат подзапроса для каждой строки из левой таблицы.	Условия соединения определяются статично, без возможности строко-ориентированных вычислений.
Производительность	Может быть менее эффективным для больших таблиц из-за строки-за-строкой выполнения подзапроса.	Обычно быстрее на больших таблицах благодаря оптимизации индексов и использованию предсказуемых наборов.
Возврат данных, если правый источник пуст	Возвращает строки из левой таблицы с NULL для правых столбцов (как в LEFT OUTER JOIN).	Возвращает строки из левой таблицы с NULL для правых столбцов, если нет соответствий.

Подзапрос в OUTER APPLY в предыдущем примере динамически находит только 3 последних заказа для каждого сотрудника. Такое поведение невозможно реализовать с помощью LEFT OUTER JOIN!

Как работает CROSS APPLY в SQL Server?

CROSS APPLY в SQL Server выполняет строко-ориентированное соединение между таблицей и табличной функцией или коррелированным подзапросом, где каждая строка из левой таблицы используется как вход для правого источника. Возвращаются только те строки левой таблицы, для которых правый источник данных возвращает результат (аналогично INNER JOIN).

Другими словами, CROSS APPLY соединяет строки левой таблицы с результатами табличной функции или подзапроса, выполняемого для каждой строки левой таблицы.

Пример использования CROSS APPLY

Рассмотрим те же самые таблицы.

Запрос:

Для каждого сотрудника получить три последних заказа, но включить в результат только сотрудников, у которых есть заказы.

Результат:

Алгоритм работы следующий:

1. Для каждой строки из таблицы Employees выполняется подзапрос.
2. Подзапрос возвращает до трех последних заказов (TOP 3) сотрудника.
3. Если у сотрудника нет заказов, он не включается в результат (как при INNER JOIN).

CROSS APPLY vs INNER JOIN

Особенность	CROSS APPLY	INNER JOIN
Работа с табличными функциями	Может использовать табличные функции или коррелированные подзапросы, зависящие от текущей строки левой таблицы.	Работает только с фиксированными наборами данных, независимыми от текущей строки.
Гибкость условий	Позволяет динамически изменять результат подзапроса для каждой строки из левой таблицы.	Условия соединения определяются статично, без возможности строко-ориентированных вычислений.
Результаты соединения	Возвращает только строки из левой таблицы, для которых правый источник данных вернул результат (аналог INNER JOIN).	Возвращает строки, соответствующие условию соединения, из обеих таблиц.
Производительность	Может быть менее эффективным для больших таблиц из-за строки-за-строкой выполнения подзапроса.	Обычно быстрее на больших таблицах благодаря оптимизации индексов и фиксированным наборам данных.

Сравнение CAST, TRY_CAST, и COALESCE в SQL Server

Эти функции используются для обработки данных, но каждая из них имеет свою специфику и область применения.

CAST

Функция CAST преобразует значение из одного типа данных в другой.

Особенности:

• Если преобразование невозможно, выбрасывает ошибку.
• Используется, когда данные гарантированно корректны.

Пример:

TRY_CAST

Функция TRY_CAST — безопасная версия CAST, которая возвращает NULL вместо ошибки, если преобразование невозможно.

Особенности:

• Используется для работы с потенциально некорректными данными.
• Помогает избежать ошибок выполнения.

Пример:

COALESCE

Функция COALESCE возвращает первое ненулевое значение из списка аргументов.

Особенности:

• Используется для обработки NULL значений.
• Не выполняет преобразование типов, но может быть объединен с CAST или TRY_CAST для этого.

Пример:

Комбинированный пример

Преобразовать строку в число и заменить NULL значением по умолчанию, если преобразование невозможно:

Вывод

• Используйте CAST, когда данные гарантированно корректны.
• Выбирайте TRY_CAST, чтобы безопасно обработать некорректные данные без ошибок.
• Применяйте COALESCE для подстановки значений вместо NULL.

OPTION CLAUSE

OPTION (MAXDOP <integer_value>)

Ограничивает максимальное количество потоков (ядер процессора), которые SQL Server может использовать для выполнения запроса.

• Используется для управления параллелизмом и минимизации влияния больших запросов на производительность других операций.
• Например, OPTION (MAXDOP 1) заставляет запрос выполняться в одном потоке, что полезно для OLTP-систем, где параллелизм может вызывать блокировки.

Настройте с учетом нагрузки на сервер и сложности запроса. Рекомендуется использовать осторожно, чтобы не подавлять параллелизм на уровне системы.

OPTION (FORCE ORDER)

Заставляет SQL Server выполнять соединения таблиц в порядке, определённом в запросе.

Полезно, когда оптимизатор запросов выбирает неэффективный порядок соединений, и вы хотите вручную указать предпочтительный порядок.

Рекомендация: Применяйте только в случае, если стандартное поведение оптимизатора приводит к низкой производительности. Перед использованием рекомендуется тестирование.

OPTION (MAX_GRANT_PERCENT = <integer_value>)

Ограничивает объем памяти (в процентах), который запрос может использовать для выполнения.

• Используется для предотвращения ситуаций, когда крупные запросы захватывают чрезмерное количество памяти, что может повлиять на другие запросы.
• Например, OPTION (MAX_GRANT_PERCENT = 10) ограничивает использование памяти запросом до 10% от доступной для сервера.

Рекомендация: Установите это значение для крупных запросов в системах с интенсивным совместным использованием ресурсов.

OPTION (MAXRECURSION <integer_value>)

Устанавливает максимальное количество итераций для рекурсивных запросов (например, CTE).

• Полезно для предотвращения бесконечной рекурсии и неконтролируемого роста потребления ресурсов.
• Например, OPTION (MAXRECURSION 100) ограничит выполнение рекурсии до 100 итераций.

Рекомендация: Настройте значение на основе глубины рекурсии в ваших данных. Значение 0 снимает ограничение.

OPTION (RECOMPILE)

Заставляет SQL Server пересчитывать план выполнения каждый раз, когда запрос выполняется.

• Полезно для запросов, параметры которых сильно варьируются, что может приводить к неэффективным планам выполнения.
• Например, запросы с разными наборами фильтров.

Рекомендация: Используйте для одноразовых запросов или тех, которые зависят от специфичных входных параметров.

OPTION (MERGE JOIN)

Принудительно использует алгоритм merge join для выполнения соединений.

Merge Join эффективен для соединений с отсортированными данными и минимальным числом операций по сравнению с Nested Loop или Hash Join.

Рекомендация: Используйте, если вы уверены, что данные предварительно отсортированы или соответствуют требованиям для Merge Join.

OPTION (MERGE UNION)

Заставляет SQL Server использовать алгоритм Merge для объединения данных из нескольких запросов.

Полезно для оптимизации операций UNION ALL, если входные данные упорядочены, что может снизить затраты на объединение.

Рекомендация: Редко используется, но может быть полезным при объединении больших объемов данных.

OPTION (HASH GROUP, FAST 10)

HASH GROUP: Принудительно использует алгоритм хэширования для группировки данных, что может быть эффективнее при обработке больших наборов данных.
FAST 10: Сообщает серверу, что нужно минимизировать задержку при возвращении первых 10 строк, даже если полный результат обрабатывается дольше.

HASH GROUP используется для оптимизации группировки, когда данные не отсортированы.
FAST <N> полезен в интерактивных системах, где важна скорость получения первых результатов.

Рекомендация: Используйте, когда это соответствует бизнес-требованиям: например, ускорьте показ первых строк в UI, сохраняя производительность.

Советы по T-SQL и Microsoft SQL Server (из книги Путь программиста T-SQL)

1. Всегда визуализируйте информацию в базе данных в виде таблиц, это поможет Вам лучше выстроить связь между таблицами, и написать необходимый SQL запрос.
2. Если Вам нужно хранить в столбце таблицы логический тип данных (только TRUE или FALSE), т.е. как Boolean в других языках программирования, то используйте тип данных BIT, не нужно использовать SMALLINT или INT.
3. Не используйте столбцы с типами float и real в условии SQL запросов, так как данные типы не хранят точных значений. Также не используйте float и real в финансовых приложениях, в операциях, связанных с округлением. Для этого лучше использовать decimal, money или smallmoney.
4. Разработку и отладку инструкций на модификацию данных лучше всего осуществлять на тестовых данных, чтобы в случае логической ошибки в запросе ничего страшного не произошло, а на «боевых» данных выполнять только готовые, проверенные, отлаженные инструкции.
5. При добавлении данных в таблицу всегда указывайте столбцы, в которые необходимо добавить данные. Это избавит Вас от путаницы и возможных проблем с результатом данной операции. Чем четче Вы будете давать инструкции SQL серверу, тем меньше у Вас будет ошибок!
6. Перед запуском инструкции UPDATE всегда проверяйте наличие и корректность условия, которое предварительно должно быть проверено на SELECT.
7. Запросы на изменение данных лучше всего проектировать так, чтобы они вставляли или изменяли как можно больше строк одной инструкцией, другими словами, если есть возможность заменить несколько инструкций обновления или добавления данных одной инструкцией, заменяйте, так эффективней по производительности.
8. При удалении данных Вы так же, как и при обновлении данных, должны четко понимать и протестировать условие на тестовых данных или с помощью инструкции SELECT, иными словами, Вы должны четко видеть, какие данные Вы сейчас удалите.
9. Всегда перед запуском массовой операции, которая требует серьезных расчетов на большом объеме данных, необходимо заранее провести обслуживание индексов, т.е. запустить процесс реорганизации и перестроения.
10. При создании таблицы всегда явно указывайте ограничение «NOT NULL» у всех столбцов, и по возможности столбцы не должны принимать значения NULL, иными словами, указывайте NOT NULL, только в исключительных случаях разрешайте хранения NULL значений.
11. В процессе планирования таблицы всегда думайте о том, какие именно данные Вам нужны в данной таблице, и на основе этого анализа создайте соответствующие ограничения, это избавит Вас от многочисленных проблем при добавлении данных и тем более, когда Вы будете анализировать эти данные, т.е. делать аналитические выборки.
12. При планировании структуры базы данных всегда выстраивайте связь между таблицами, которые логически связаны между собой, и создавайте соответствующие ограничения.
13. Всегда комментируйте свой код, свои SQL инструкции, там, где у Вас или у другого программиста, который будет читать это код, могут возникнуть вопросы.
14. Всегда перед запуском новой SQL инструкции, в которой есть цикл, проверяйте условие, при котором цикл должен завершиться, для того чтобы избежать попадания в бесконечный цикл.
15. Если в SQL инструкции используются сложные и важные алгоритмы, всегда используйте конструкцию обработки ошибок, тем самым Вы избежите непредвиденных результатов.
16. Любой код, который полностью повторяется 2 и более раз в одной или нескольких SQL инструкциях, выносите в функцию, т.е. напишите функцию, и везде, где нужно использовать этот код, вызывайте эту функцию.
17. При внесении изменений в алгоритм работы функции помните о том, где и для каких целей эта функция используется, так как внесенные изменения отразятся во всех инструкциях, в которых задействована эта функция. Иными словами, не допускайте ситуаций, когда изменение алгоритма работы функции вносит корректные поправки в одни инструкции, для которых Вы и внесли эти изменения, а для других инструкций изменение алгоритма влечет некорректную работу этих SQL инструкций.
18. Если параметров в хранимой процедуре много, и некоторые из них являются необязательными, рекомендую при вызове таких процедур перечислять название параметров. Если параметров всего 1 или 2, то можно название параметров и не указывать, а использовать последовательность передачи значений.
19. При разработке триггера, который реализует некие бизнес правила, всегда четко планируйте и тестируйте все действия триггера, в противном случае в определенных условиях, которые Вы не учтете, триггер может скрытно, т.е. незаметно для Вас, вносить некорректные изменения в базу данных, а когда Вы обнаружите эти некорректные данные или нарушение целостности данных, Вам нужно будет еще отследить причины этих изменений, ведь с первого взгляда явных причин Вы не увидите. Последствия таких ошибок в работе триггера могут быть очень серьезные.
20. Если есть возможность выполнить задачу или реализовать алгоритм без использования курсоров, то делайте это без курсоров. Курсоры применяйте только в тех случаях, когда другого решения у Вас нет. Так как если в алгоритме работы приложения будет задействовано слишком много курсоров, это заметно замедлит работу приложения. Иными словами, используйте курсоры только в самых крайних случаях.
21. Если в одном SQL запросе SELECT Вы прибегаете к использованию нескольких подзапросов в секциях FROM или JOIN, выносите данные подзапросы в обобщенное табличное выражение (конструкция WITH) – впоследствии это Вам значительно упростит понимание логики запроса.
22. В целях безопасности и сохранности данных, рекомендуется четко планировать права доступа для пользователей. Например, не назначать всем пользователям роль db_owner или серверную роль sysadmin, так как пользователи, сами того не зная, могут выполнить действия, которые повлекут за собой неправомерное или некорректное изменение данных или вовсе потерю данных. Последствия в таких случаях могут быть очень серьезные. Поэтому назначайте пользователям только те права, которые им необходимы для выполнения их непосредственной работы.
23. Если у Вас на одном экземпляре SQL Server несколько баз данных, всегда проверяйте контекст подключения к базе данных перед запуском SQL инструкций. Также рекомендуется в инструкциях для администратора сервера принудительно использовать команду USE для точного указания базы данных, для которой предназначена эта инструкция.
24. Всегда создавайте архивы баз данных. Уделите время и разработайте план и схему резервирования базы данных, у инструкции BACKUP DATABASE много параметров, которые в этом Вам помогут. В итоге у Вас всегда должен быть актуальный архив базы данных на случай непредвиденных ситуаций, данный архив должен храниться в отдельном хранилище (сервере), а это хранилище или сервер должен располагаться в помещении, отличном от помещения, в котором расположен сервер с экземпляром SQL Server.
25. Операцию сжатия базы данных не рекомендуется выполнять слишком часто, так как она может вызвать фрагментацию индексов и замедлить работу базы данных. Поэтому выполняйте операцию сжатия базы данных только в самых крайних случаях.

Литература, использованная при подготовки статьи

1. Путь программиста T-SQL. Самоучитель по языку Transact-SQL.
   Виталий Трунин