для чего нужен with sql

Конструкция WITH в T-SQL или обобщенное табличное выражение (ОТВ)

Всем привет! Тема сегодняшнего материала будет посвящена обобщенным табличным выражениям языка T-SQL, мы с Вами узнаем, что это такое, а также рассмотрим примеры написания запросов с использованием этих самых обобщённых табличных выражений.

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

Что такое обобщенное табличное выражение?

Common Table Expression (CTE) или обобщенное табличное выражение (OTB) – это временные результирующие наборы (т.е. результаты выполнения SQL запроса), которые не сохраняются в базе данных в виде объектов, но к ним можно обращаться.

Главной особенностью обобщенных табличных выражений является то, что с помощью них можно писать рекурсивные запросы, но об этом чуть ниже, а сейчас давайте поговорим о том, в каких случаях нам могут пригодиться OTB, в общем, для чего они предназначены:

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

Синтаксис:

После обобщенного табличного выражения, т.е. сразу за ним должен идти одиночный запрос SELECT, INSERT, UPDATE, MERGE или DELETE.

Какие бывают обобщенные табличные выражения?

Они бывают простые и рекурсивные.

Простые не включают ссылки на самого себя, а рекурсивные соответственно включают.

Рекурсивные ОТВ используются для возвращения иерархических данных, например, классика жанра это отображение сотрудников в структуре организации (чуть ниже мы это рассмотрим).

Примечание! Все примеры ниже будут рассмотрены в MS SQL Server 2008 R2.

В качестве тестовых данных давайте использовать таблицу TestTable, которая будет содержать идентификатор сотрудника, его должность и идентификатор его начальника.

Как видите, у директора отсутствует ManagerID, так как у него нет начальника. А теперь переходим к примерам.

Пример простого обобщенного табличного выражения

Для примера давайте просто выведем все содержимое таблицы TestTable с использованием обобщенного табличного выражения

Где TestCTE это и есть псевдоним результирующего набора, к которому мы и обращаемся.

В данном случае мы могли и не перечислять имена столбцов, так как они у нас уникальны. Можно было просто написать вот так:

Пример рекурсивного обобщенного табличного выражения

Теперь допустим, что нам необходимо вывести иерархический список сотрудников, т.е. мы хотим видеть, на каком уровне работает тот или иной сотрудник. Для этого пишем рекурсивный запрос:

В итоге, если мы захотим, мы можем легко получить список сотрудников определенного уровня, например, нам нужны только начальники отделов, для этого мы просто в указанный выше запрос добавим условие WHERE LevelUser = 1

При написании рекурсивного ОТВ нужно быть внимательным, так как неправильное его составление может привести к бесконечному циклу. Поэтому для этих целей есть опция MAXRECURSION, которая может ограничивать количество уровней рекурсии. Давайте представим, что мы не уверены, что написали рекурсивное обобщенное выражение правильно и для отладки напишем инструкцию OPTION (MAXRECURSION 5), т.е. отобразим только 5 уровня рекурсии, и если уровней будет больше, SQL инструкция будет прервана.

Запрос у нас отработал, что говорит о том, что мы написали его правильно и соответственно OPTION (MAXRECURSION 5) можно смело убрать.

Заметка! Для комплексного изучения языка SQL и T-SQL рекомендую посмотреть мои видеокурсы по T-SQL, в которых используется последовательная методика обучения специально для начинающих.

Источник

Руководство по использованию предложения WITH в SQL

Я понимаю, как использовать WITH предложение для рекурсивных запросов (!!), но у меня возникли проблемы с пониманием его общего использования / мощности.

например, следующий запрос обновляет одну запись, идентификатор которой определяется с помощью подзапроса, возвращающего идентификатор первой записи по метке времени:

будет ли это хорошим кандидатом для использования WITH обертка вместо относительно уродливого подзапроса? Если так, то почему?

2 ответов

Если может быть одновременный доступ на запись к задействованным таблицам, условия гонки в вышеуказанных следующих запросах. Подумайте:

ваш пример можете используйте CTE( общее табличное выражение), но это не даст вам ничего, что подзапрос не мог бы сделать:

кстати, возвращаемая строка будет обновлено версия.

Если вы хотели вставить возвращенную строку в другую таблицу, вот где предложение WITH становится существенным:

запросы на изменение данных с помощью CTE возможны с PostgreSQL 9.1 или более поздней версии.
Читать больше в превосходном руководстве.

WITH позволяет определить «временные таблицы» для использования в SELECT запрос. Например, недавно я написал такой запрос, чтобы вычислить изменения между двумя наборами:

если Я правильно понимаю ваш запрос, он делает следующее:

найдите самую старую строку в global.перспектива, чей статус «новый» или «сброс».

отметьте его, добавив звездочку к его статусу

верните строку (включая нашу настройку в status ).

не думаю WITH упростит что-нибудь в вашем случае. Это может быть немного более элегантно использовать FROM статья, хотя:

_{непроверенными. Дайте мне знать, если это работает.}

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

это можно легко расширить для обновления нескольких строк. В вашей версии, которая использует выражение подзапроса, запрос завершится ошибкой, если подзапрос будет изменен на несколько строк.

я не псевдоним global.prospect в подзапросе, поэтому его немного легче читать. С это использует FROM предложение, вы получите ошибку, если вы случайно ссылаетесь на обновляемую таблицу.

в вашей версии выражение подзапроса встречается для каждого отдельного элемента. Хотя PostgreSQL должен оптимизировать это и оценивать выражение только один раз, эта оптимизация исчезнет, если вы случайно ссылаетесь на столбец в psp или добавьте выражение volatile.

Источник

WITH обобщенное_табличное_выражение (Transact-SQL)

Задается временно именованный результирующий набор, называемый обобщенным табличным выражением (ОТВ). Он получается при выполнении простого запроса и определяется в области выполнения одиночной инструкции SELECT, INSERT, UPDATE, DELETE или MERGE. Это предложение может использоваться также в инструкции CREATE VIEW как часть определяющей ее инструкции SELECT. Обобщенное табличное выражение может включать ссылки на само себя. Такое выражение называется рекурсивным обобщенным табличным выражением.

Синтаксические обозначения в Transact-SQL

Синтаксис

Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.

Аргументы

column_name
Задается имя столбца в обобщенном табличном выражении. Повторяющиеся имена в определении одного обобщенного табличного выражения не допускаются. Количество заданных имен столбцов должно совпадать с количеством столбцов в результирующем наборе CTE_query_definition. Список имен столбцов необязателен только в том случае, если всем результирующим столбцам в определении запроса присвоены уникальные имена.

CTE_query_definition
Задается инструкция SELECT, результирующий набор которой заполняет обобщенное табличное выражение. Инструкция SELECT для CTE_query_definition должна соответствовать таким же требованиям, что и при создании представления, за исключением того, что обобщенное табличное выражение (ОТВ) не может определять другое ОТВ. Дополнительные сведения см. в подразделе «Замечания» и разделе CREATE VIEW (Transact-SQL).

Если определено несколько параметров CTE_query_definition, определения запроса должны быть соединены одним из следующих операторов над множествами: UNION ALL, UNION, EXCEPT или INTERSECT.

Remarks

Рекомендации по созданию и использованию обобщенных табличных выражений

Следующие рекомендации относятся к нерекурсивным обобщенным табличным выражениям. Рекомендации, применимые к рекурсивным обобщенным табличным выражениям, см. в расположенном ниже разделе Рекомендации по определению и использованию рекурсивных обобщенных табличных выражений.

Задание в одном обобщенном табличном выражении нескольких предложений WITH недопустимо. Например, если CTE_query_definition содержит вложенный запрос, этот вложенный запрос не может содержать вложенное предложение WITH, определяющее другое обобщенное табличное выражение.

Следующие предложения не могут использоваться в CTE_query_definition:

ORDER BY (за исключением случаев задания предложения TOP )

Предложение OPTION с указаниями запроса

Если обобщенное табличное выражение используется в инструкции, являющейся частью пакета, то за инструкцией, стоящей перед ней, должен следовать символ точки с запятой.

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

В обобщенном табличном выражении могут быть ссылки на таблицы, находящиеся на удаленных серверах.

При выполнении обобщенного табличного выражения (ОТВ) между указаниями, ссылающимися на ОТВ, может быть конфликт с другими указаниями, обнаруживаемыми, когда ОТВ обращаются к их базовым таблицам, так же, как если бы указания ссылались на представления в запросах. Когда это происходит, запрос возвращает ошибку.

Рекомендации по созданию и использованию рекурсивных обобщенных табличных выражений

Следующие рекомендации применимы к определению рекурсивных обобщенных табличных выражений.

Определение рекурсивного обобщенного табличного выражения должно содержать по крайней мере два определения обобщенного табличного выражения запросов — закрепленный элемент и рекурсивный элемент. Может быть определено несколько закрепленных элементов и рекурсивных элементов, однако все определения запросов закрепленного элемента должны быть поставлены перед первым определением рекурсивного элемента. Все определения обобщенных табличных выражений запросов (ОТВ) являются закрепленными элементами, если только они не ссылаются на само ОТВ.

Закрепленные элементы должны объединяться одним из следующих операторов над множествами: UNION ALL, UNION, INTERSECT или EXCEPT. UNION ALL является единственным оператором над множествами, который может находиться между последним закрепленным элементом и первым рекурсивным элементом, а также может применяться при объединении нескольких рекурсивных элементов.

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

Тип данных столбца в рекурсивном элементе должен совпадать с типом данных соответствующего столбца в закрепленном элементе.

Предложение FROM рекурсивного элемента должно ссылаться на обобщенное табличное выражение expression_name только один раз.

Следующие элементы недопустимы в определении CTE_query_definition рекурсивного элемента:

PIVOT (Если уровень совместимости базы данных имеет значение 110 или больше. См. раздел Критические изменения в функциях компонента ядра СУБД в SQL Server 2016).

Указание, применимое к рекурсивной ссылке на обобщенное табличное выражение в определении CTE_query_definition.

Следующие рекомендации применимы к использованию рекурсивных обобщенных табличных выражений.

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

Курсоры могут определяться на запросах при помощи обобщенных табличных выражений. Обобщенное табличное выражение является аргументом select_statement, который определяет результирующий набор курсора. Для рекурсивных обобщенных табличных выражений допустимы только однонаправленные и статические курсоры (курсоры моментального снимка). Если в рекурсивном обобщенном табличном выражении указан курсор другого типа, тип курсора преобразуется в статический.

Возможности и ограничения общих табличных выражений в Azure Synapse Analytics и Система платформы аналитики (PDW)

Текущая реализация обобщенных табличных выражений в Azure Synapse Analytics и Система платформы аналитики (PDW) имеет следующие возможности и ограничения:

Обобщенное табличное выражение можно задать в инструкции CREATE TABLE AS SELECT (CTAS).

Обобщенное табличное выражение можно задать в инструкции CREATE REMOTE TABLE AS SELECT (CRTAS).

Обобщенное табличное выражение можно задать в инструкции CREATE EXTERNAL TABLE AS SELECT (CETAS).

Обобщенное табличное выражение может ссылаться на внешнюю таблицу.

Обобщенное табличное выражение может ссылаться на внешнюю таблицу.

В обобщенном табличном выражении можно задать несколько определений запросов обобщенных табличных выражений (ОТВ).

Обобщенное табличное выражение, которое включает ссылки на себя (рекурсивное обобщенное табличное выражение), не поддерживается.

Если обобщенное табличное выражение используется в инструкции, являющейся частью пакета, то за инструкцией, стоящей перед ней, должен следовать символ точки с запятой.

Примеры

A. Создание простого обобщенного табличного выражения

В следующем примере выводится общее количество заказов на продажу в год для каждого коммерческого представителя в Компания Adventure Works Cycles.

Б. Использование обобщенного табличного выражения для ограничения общего и среднего количества отчетов

В следующем примере выводится среднее количество заказов на продажу за все годы для коммерческих представителей.

В. Использование нескольких определений ОТВ (обобщенных табличных выражений) в одном запросе

В следующем примере показано, как определить несколько ОТВ в одном запросе. Обратите внимание, что для разделения определений запросов обобщенных табличных выражений используется запятая. Функция FORMAT, используемая для отображения денежных сумм в формате валюты, доступна в SQL Server 2012 и более поздних версиях.

Здесь приводится частичный результирующий набор.

Г. Использование рекурсивного обобщенного табличного выражения для отображения нескольких уровней рекурсии

Использование рекурсивного обобщенного табличного выражения для отображения двух уровней рекурсии

В следующем примере представлены руководители и отчитывающиеся перед ними служащие. Количество возвращаемых уровней ограничено двумя.

Использование рекурсивного обобщенного табличного выражения для отображения иерархического списка

В следующем примере добавляются имена руководителя и сотрудников, а также соответствующие им должности. Иерархия руководителей и служащих дополнительно выделяется с помощью соответствующих отступов на каждом уровне.

Использование подсказки MAXRECURSION для отмены инструкции

Подсказка MAXRECURSION может использоваться для предотвращения входа в бесконечный цикл из-за неверно сформированного рекурсивного CTE-выражения. В следующем примере преднамеренно формируется бесконечный цикл и используется указание MAXRECURSION для ограничения числа уровней рекурсии двумя.

После исправления ошибки в коде подсказка MAXRECURSION больше не нужна. В следующем примере приводится правильный код.

Д. Использование обобщенного табличного выражения для выборочного прохождения рекурсивной связи в инструкции SELECT

Е. Использование рекурсивного обобщенного табличного выражения в инструкции UPDATE

З. Использование нескольких привязок и рекурсивных элементов

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

I. Использование аналитических функций в рекурсивном обобщенном табличном выражении

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

Следующие результаты являются ожидаемыми результатами выполнения запроса.

Следующие результаты являются фактическими результатами выполнения запроса.

N возвращает 1 для каждого прохода рекурсивной части ОТВ, так как в ROWNUMBER передается только подмножество данных для данного уровня рекурсии. Для каждой итерации рекурсивной части запроса в ROWNUMBER передается только одна строка.

Примеры: Azure Synapse Analytics и Система платформы аналитики (PDW)

К. Использование обобщенного табличного выражения в инструкции CTAS

В следующем примере создается новая таблица, содержащая общее количество заказов на продажу в год для каждого коммерческого представителя в Компания Adventure Works Cycles.

Л. Использование обобщенного табличного выражения в инструкции CETAS

В следующем примере создается новая внешняя таблица, содержащая общее количество заказов на продажу в год для каждого коммерческого представителя в Компания Adventure Works Cycles.

М. Использование нескольких разделенных запятыми обобщенных табличных выражений в инструкции

В следующем примере показано включение двух обобщенных табличных выражений в одну инструкцию. Обобщенные табличные выражения не поддерживают вложение (рекурсию).

Источник

SQL-Ex blog

Новости сайта «Упражнения SQL», статьи и переводы

Основы использования хинта NOLOCK в SQL Server

Основная идея механизма блокировок в SQL Server состоит в контроле согласованности транзакций. Согласно этому принципу, если процессу требуется выполнить операции вставки, удаления или обновления, ядро SQL Server блокирует строку или строки и не позволяет другим процессам получить доступ к данным до завершения транзакции. При определенных обстоятельствах этот механизм блокировок может привести к падению производительности, например, при множестве конкурирующих процессов. В результате вы можете столкнуться с проблемой тупиковой ситуации вашей базы данных (это такая ситуация, когда две транзакции требуют доступа к одним и тем же данным в одно и то же время). В этой статье мы уделим внимание тому, как избежать проблем блокировки с помощью хинта NOLOCK. Сначала давайте познакомимся с основными положениями и деталями методологии «грязного чтения», поскольку хинт NOLOCK может приводить к грязному чтению.

Грязное чтение: В этой методологии процесс считывает незафиксированные данные и не обращает внимания на открытые транзакции, поэтому блокировки не вызывают никаких проблем в процессе чтения. Таким образом, этот тип чтения снижает уровень блокировок. Однако грязное чтение имеет как положительные, так и отрицательные стороны, поскольку грязное чтение может вызывать проблемы несогласованности данных в результирующем наборе оператора SELECT. Как отмечалось ранее, этот результирующий набор может включать следы незафиксированных транзакций, и мы должны принимать это в расчет, решая использовать этот вид чтения. Мы не можем быть уверены в реальности строк, которые мы получаем при грязном чтении, поскольку для этих строк может быть выполнен откат. С другой стороны, этот тип чтения позволяет избежать проблем с блокировками и увеличить производительность SQL Server.

NOLOCK: По умолчанию SQL Server использует уровень изоляции Read Committed (чтение зафиксированных транзакций), и этот уровень изоляции не позволяет читать объекты, которые заблокированы незавершенными транзакциями. Кроме того, эти заблокированные объекты могут изменяться в соответствии с эскалацией блокировки.

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

В этом случае User2 ждет, по меньшей мере, 10 секунд, а затем транзакция откатывается пользователем user1, после чего пользователь user2 может прочитать строку, отмеченную зеленым, поскольку блокировка строки снимается пользователем user1. Это поведение по умолчанию уровня изоляции Read Committed в SQL Server.

Теперь продемонстрируем этот случай в SQL Server. Сначала создадим таблицу FruitSales и добавим в неё несколько строк.

Как вы можете увидеть, второй запрос ожидает до тех, пока пользователь user1 не выполнит откат транзакции.

Теперь мы добавим хинт NOLOCK в оператор SELECT пользователя user2, а затем выполним UPDATE пользователя user1 с последующим оператором SELECT пользователя user2.

Теперь посмотрим на результат выполнения оператора SELECT. Оператор SELECT пользователя user2 возвращает значение 20 столбца SalesTotal, хотя реальное значение осталось равным 8. Запомните, что если вы используете табличный хинт NOLOCK в запросе на выборку, то можете столкнуться с подобным типом несоответствия результатов.

Совет. Ключевое слово «WITH» является устаревшим, поэтому Майкрософт рекомендует не использовать его в новых проектах баз данных и удалить из текущих разработок. Вы можете использовать хинт NOLOCK без слова «WITH».

Кроме этого, табличный хинт READUNCOMMITTED эквивалентен хинту NOLOCK, и мы можем использовать его вместо NOLOCK.

Несмотря на это, существует случай, когда хинт NOLOCK не в состоянии преодолеть барьер блокировки. Если некоторый процесс изменяет структуру таблицы, NOLOCK не может изменить тип блокировки и не позволит продолжить операцию чтения. Причина заключается в том, что хинт NOLOCK ориентирован на блокировки Sch-S (стабильность схемы), а оператор ALTER TABLE накладывает Sch-M блокировку (модификация схемы), так что имеет место конфликт.

Сначала мы определим Object_id (идентификатор объекта) таблицы FruitSales с помощью следующего запроса.

Запустите следующий запрос user1, а затем запрос user2. В результате запрос user2 будет ожидать завершения процесса изменения таблицы пользователем user1.

Откройте новое окно запроса и выполните следующий код. Этот запрос поможет выяснить тип блокировки запросов user1 и user2.

Теперь мы сверимся с матрицей совместимости блокировок для SCH-M и SCH-S. Матрица указывает на конфликт между SCH-M и SCH-S.

Заключение

Обратные ссылки

Нет обратных ссылок

Комментарии

Показывать комментарии Как список | Древовидной структурой

Аноним on Четверг, 7 октября. 2021 :

В статье допущена небольшая смысловая ошибка.

«Совет. Ключевое слово «WITH» является устаревшим, поэтому Майкрософт рекомендует не использовать его в новых проектах баз данных и удалить из. «

В справке Microsoft указано следующее:
Omitting the WITH keyword is a deprecated feature: This feature will be removed in a future version of Microsoft SQL Server.

Автор не разрешил комментировать эту запись

Источник

Табличные указания (Transact-SQL)

Табличные подсказки переопределяют поведение оптимизатора запросов по умолчанию на время выполнения инструкции языка обработки данных (DML). Для этого указываются способ блокировки, один или более индексов, операция обработки запроса, например сканирования таблицы или поиска в индексе, или другие параметры. Табличные указания задаются в предложении FROM инструкции DML и относятся только к таблицам и представлениям, на которые ссылается это предложение.

Оптимизатор запросов SQL Server обычно выбирает наилучший план выполнения запроса. Поэтому одсказки рекомендуется использовать только опытным разработчикам и администраторам баз данных в качестве последнего средства.

Применимо к:

Синтаксические обозначения в Transact-SQL

Синтаксис

Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.

Аргументы

WITH ( ) [ [ , ]. n ]
Табличные указания, за некоторыми исключениями, поддерживаются в предложении FROM только в случае, если они задаются с ключевым словом WITH. Табличные указания также необходимо заключать в скобки.

Пропуск ключевого слова WITH является устаревшей возможностью: В будущей версии Microsoft SQL Server этот компонент будет удален. Избегайте использования этого компонента в новых разработках и запланируйте изменение существующих приложений, в которых он применяется.

Если подсказка указана с другим параметром, ее необходимо указывать с ключевым словом WITH:

Между табличными подсказками рекомендуется ставить запятые.

Разделение подсказок пробелами, а не с помощью запятых, является устаревшей возможностью. В будущей версии Microsoft SQL Server этот компонент будет удален. Избегайте использования этого компонента в новых разработках и запланируйте изменение существующих приложений, в которых он применяется.

NOEXPAND
Указывает, что при обработке запроса оптимизатором запросов никакие индексированные представления не расширяются для доступа к базовым таблицам. Оптимизатор запросов обрабатывает представление так же, как и таблицу с кластеризованным индексом. Аргумент NOEXPAND применяется только для индексированных представлений. Дополнительные сведения см. в разделе Использование NOEXPAND.

Если существует кластеризованный индекс, INDEX(0) вызывает проверку кластеризованного индекса, а INDEX(1) — проверку кластеризованного индекса или поиск по нему. Если кластеризованный индекс не существует, INDEX(0) вызывает проверку таблицы, а INDEX(1) интерпретируется как ошибка.

Если в отдельном списке указаний используются несколько индексов, повторяющиеся индексы пропускаются, а остальные используются для получения строк из таблицы. Порядок индексов в указании индекса имеет значение. Несколько указаний индекса также принудительно выполняют операции И с индексами, и оптимизатор запросов применяет столько условий, сколько возможно для каждого из индексов, к которым он получает доступ. Если коллекция индексов с подсказками не включает все указанные в запросе столбцы, то выборка для получения остальных столбцов выполняется после того, как компонентом Компонент SQL Server Database Engine будут получены все индексированные столбцы.

Если указание индекса, ссылающееся на несколько индексов, используется в таблице фактов в соединении типа «звезда», оптимизатор не учитывает индекс и возвращает предупреждение. Кроме того, выполнение операции ИЛИ с индексами также не разрешено для таблицы с заданным указанием индекса.

Максимальное число индексов в табличном указании равно 250 некластеризованным индексам.

KEEPIDENTITY
Применяется только в инструкции INSERT, когда параметр BULK используется с OPENROWSET.

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

Если файл данных не содержит значений столбца идентификаторов таблицы или представления, а столбец идентификаторов не является последним в таблице, этот столбец необходимо пропустить. Дополнительные сведения см. в разделе Использование файла форматирования для пропуска поля данных (SQL Server). Если столбец идентификаторов успешно пропущен, то оптимизатор запросов автоматически назначает уникальные значения для столбца идентификаторов в импортируемые строки таблицы.

Дополнительные сведения о проверке идентифицирующего значения для таблицы см. в разделе DBCC CHECKIDENT (Transact-SQL).

KEEPDEFAULTS
Применяется только в инструкции INSERT, когда параметр BULK используется с OPENROWSET.

Указывает на вставку установленного по умолчанию значения столбца таблицы, если таковое имеется, вместо значения NULL, применяемого в случае, когда запись данных не содержит значения для этого столбца.

Пример использования этого указания в инструкции INSERT… Дополнительные сведения об инструкции SELECT * FROM OPENROWSET(BULK. ) см. в разделе Сохранение значений NULL или использование значений по умолчанию при массовом импорте данных (SQL Server).

Начиная с SQL Server 2008 R2 с пакетом обновления 1 (SP1), также могут указываться параметры индекса. В таком случае оптимизатор запросов будет использовать при выполнении операций поиска в индексе по указанному индексу как минимум все указанные столбцы индекса.

index_value
Имя или значение идентификатора индекса. Указывать идентификатор индекса 0 (куча) нельзя. Чтобы получить имя или идентификатор индекса, запросите представление каталога sys.indexes.

index_column_name
Это имя столбца индекса, включаемого в операцию поиска. Указание FORCESEEK с параметрами индекса аналогично использованию FORCESEEK с указанием INDEX. Но более эффективного контроля над путем доступа, который использует оптимизатор запросов, можно добиться указанием и индекса, в котором следует провести поиск, и столбцов индекса, которые предполагается использовать в операции поиска. При необходимости оптимизатор может задействовать дополнительные столбцы. Например, если указан некластеризованный индекс, то оптимизатор может в дополнение к указанным столбцам выбрать использование ключевых столбцов кластеризованного индекса.

Подсказка FORCESEEK может быть указана следующим образом.

При использовании указания FORCESEEK (с указанием параметров индексов или без них) руководствуйтесь следующими рекомендациями:

Если FORCESEEK указывается с параметрами индекса, применяются следующие ограничения и рекомендации:

Указание FORCESEEK с параметрами ограничивает число планов, которые могут быть использованы оптимизатором, в отличие от указания FORCESEEK без параметров. Это может привести к тому, что ошибка Plan cannot be generated возникает в нескольких случаях. В будущих выпусках внутренние изменения оптимизатора запросов могут привести к увеличению числа этих планов.

FORCESCAN Применимо к: SQL Server 2008 R2 с пакетом обновления 1 (SP1) и выше. Указывает, что в качестве пути доступа к ссылочным таблицам или представлениям оптимизатор запросов использует только операцию сканирования в индексе. Указание FORCESCAN может оказаться полезным в тех запросах, где оптимизатор недооценивает число затрагиваемых строк и выбирает операцию поиска, а не сканирования. В этом случае объем памяти, выделенный для данной операции, будет недостаточным, что повлияет на производительность запроса.

Указание FORCESCAN может быть указано с указанием INDEX или без него. В сочетании с указанием индекса ( INDEX = index_name, FORCESCAN ) оптимизатор запросов рассматривает пути доступа для сканирования через указанный индекс при доступе к упоминаемой таблице. Указание FORCESCAN может задаваться с указанием индекса INDEX(0) для принудительного сканирования базовой таблицы.

Для секционированных таблиц и индексов указание FORCESCAN применяется после устранения секционирования посредством вычисления предиката запроса. Это означает, что сканирование выполняется только в оставшихся секциях, а не во всей таблице.

Указание FORCESCAN имеет следующие ограничения:

HOLDLOCK
Равнозначен аргументу SERIALIZABLE. Дополнительные сведения об аргументе SERIALIZABLE см. далее в этом разделе. Аргумент HOLDLOCK применяется только к таблице или представлению, для которых он задан, и только на время транзакции, определенной в использующей его инструкции. Аргумент HOLDLOCK нельзя использовать в инструкции SELECT, включающей параметр FOR BROWSE.

IGNORE_CONSTRAINTS
Применяется только в инструкции INSERT, когда параметр BULK используется с OPENROWSET.

Указывает, что при операции массового импорта будут пропускаться какие-либо ограничения на таблицу. По умолчанию INSERT проверяет ограничения уникальности и проверочные ограничения и ограничения первичных и внешних ключей. Если для операции массового импорта задан параметр IGNORE_CONSTRAINTS, инструкция INSERT будет пропускать ограничения в целевой таблице. Обратите внимание, что нельзя отключить ограничения UNIQUE, PRIMARY KEY или NOT NULL.

Отключение ограничений CHECK и FOREIGN KEY может потребоваться, если введенные данные содержат нарушающие ограничения строки. При отключении ограничений CHECK и FOREIGN KEY можно импортировать данные, а затем произвести очистку данных с помощью инструкций Transact-SQL.

Однако при пропуске ограничений CHECK и FOREIGN KEY после операции каждое пропущенное ограничение помечается как is_not_trusted в представлении каталога sys.check_constraints или sys.foreign_keys. Рано или поздно придется проверить всю таблицу на соответствие ограничениям. Если таблица не была пустой перед операцией массового импорта, затраты на повторную проверку ограничений могут превысить затраты от применения ограничений CHECK и FOREIGN KEY к добавочным данным.

IGNORE_TRIGGERS
Применяется только в инструкции INSERT, когда параметр BULK используется с OPENROWSET.

Указывает, что при операции объемного импорта не будут учитываться какие-либо триггеры, определенные для таблицы. По умолчанию для инструкции INSERT применяются триггеры.

Аргумент IGNORE_TRIGGERS следует использовать только в случае, когда приложение не зависит от каких-либо триггеров и важно максимизировать производительность.

NOLOCK
Равнозначен аргументу READUNCOMMITTED. Дополнительные сведения об аргументе READUNCOMMITTED см. далее в этом разделе.

Для инструкций UPDATE и DELETE. В будущей версии Microsoft SQL Server этот компонент будет удален. Избегайте использования этого компонента в новых разработках и запланируйте изменение существующих приложений, в которых он применяется.

PAGLOCK
Применяет блокировку страниц вместо стандартной блокировки строк или ключей, а также вместо блокировки отдельной таблицы. По умолчанию используется режим блокировки, соответствующий операции. При указании блокировок в транзакциях, выполняемых с уровнем изоляции SNAPSHOT, они применяются только в том случае, когда подсказка PAGLOCK используется в сочетании с другими табличными подсказками, требующими блокировки, например UPDLOCK или HOLDLOCK.

READCOMMITTED
Указывает, что операции чтения соответствуют правилам для уровня изоляции READ COMMITTED путем использования блокировки или управления версиями строк. Если параметр базы данных READ_COMMITTED_SNAPSHOT установлен в значение OFF, компонент Компонент Database Engine устанавливает совмещаемую блокировку по мере чтения данных и снимает блокировку при завершении операции чтения. Если значение параметра базы данных READ_COMMITTED_SNAPSHOT равно ON, компонент Компонент Database Engine не накладывает блокировок и использует управление версиями строк. Дополнительные сведения об уровнях изоляции см. в разделе SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

Для инструкций UPDATE и DELETE. В будущей версии Microsoft SQL Server этот компонент будет удален. Избегайте использования этого компонента в новых разработках и запланируйте изменение существующих приложений, в которых он применяется.

READCOMMITTEDLOCK
Указывает, что операции чтения соответствуют правилам для уровня изоляции READ COMMITTED путем использования блокировки. Компонент Компонент Database Engine накладывает совмещаемые блокировки по мере чтения данных и снимает их после завершения операции чтения вне зависимости от значения параметра базы данных READ_COMMITTED_SNAPSHOT. Дополнительные сведения об уровнях изоляции см. в разделе SET TRANSACTION ISOLATION LEVEL (Transact-SQL). Это указание не может задаваться в целевой таблице инструкции INSERT, в таком случае возвращается ошибка 4140.

READPAST
Указывает, что компонент Компонент Database Engine не считывает строки и страницы, заблокированные другими транзакциями. Если указан аргумент READPAST, блокировки уровня строк будут пропускаться, а блокировки уровня страниц — не будут. Компонент Компонент Database Engine будет пропускать строки вместо блокировки текущей транзакции до тех пор, пока блокировки не будут сняты. Например, предположим, что в таблице T1 есть один целочисленный столбец со значениями 1, 2, 3, 4, 5. Если транзакция A изменит значение 3 на 8, но еще не будет зафиксирована, то инструкция SELECT * FROM T1 (READPAST) возвратит значения 1, 2, 4, 5. Параметр READPAST главным образом используется для устранения конфликта блокировок при реализации рабочей очереди, использующей таблицу SQL Server. Средство чтения очереди, использующее аргумент READPAST, пропускает прошлые записи очереди, заблокированные другими транзакциями, до следующей доступной записи очереди, не дожидаясь, пока другие транзакции снимут свои блокировки.

Аргумент READPAST можно задать для любой таблицы, к которой обращается инструкция UPDATE или DELETE, и к любой таблице, на которую ссылается предложение FROM. Если аргумент READPAST задан в инструкции UPDATE, он применяется только при считывании данных для идентификации подлежащих обновлению записей вне зависимости от того, где он указан в инструкции. Аргумент READPAST для таблиц из предложения INTO инструкции INSERT задать нельзя. Операции обновления или удаления, использующие аргумент READPAST, могут блокироваться либо при считывании внешних ключей или индексированных представлений, либо при изменении вторичных индексов.

Аргумент READPAST можно указывать только в транзакциях, выполняемых на уровнях изоляции READ COMMITTED или REPEATABLE READ. При указании подсказки READPAST в транзакциях, выполняемых с уровнем изоляции SNAPSHOT, она должна использоваться в сочетании с другими табличными подсказками, требующими блокировки, например UPDLOCK или HOLDLOCK.

Табличное указание READPAST нельзя указать, если для параметра базы данных READ_COMMITTED_SNAPSHOT установлено значение ON и выполняется одно из следующих условий:

Чтобы в этих случаях указать подсказку READPAST, удалите табличную подсказку READCOMMITTED (если существует) и включите в запрос табличную подсказку READCOMMITTEDLOCK.

READUNCOMMITTED
Указывает, что чтение недействительных результатов разрешено. Для предотвращения ситуаций, когда другие транзакции изменяют данные, считанные текущей транзакцией, не накладываются совмещаемые блокировки, а монопольные блокировки других транзакций не мешают текущей транзакции считывать заблокированные данные. Разрешение чтения измененных результатов может привести к повышению параллелизма за счет считывания изменений данных, откат которых произведен другими транзакциями. Это в свою очередь может сопровождаться ошибками транзакции, представлением пользователю незафиксированных данных, повторным появлением некоторых записей или их отсутствием.

Указания READUNCOMMITTED и NOLOCK применяются только к блокировкам данных. Все запросы, включая запросы с указаниями READUNCOMMITTED и NOLOCK, получают блокировку Sch-S (стабильность схемы) в процессе компиляции и выполнения. Поэтому запросы блокируются, если параллельная транзакция удерживает в таблице блокировку Sch-M (изменение схемы). Например, операция языка DDL получает блокировку Sch-M до того, как она изменяет данные схемы. Все параллельные запросы, включая выполняемые с указаниями READUNCOMMITTED или NOLOCK, блокируются при попытке получить блокировку Sch-S. И наоборот, запрос, удерживающий блокировку Sch-S, блокирует параллельную транзакцию, которая пытается получить блокировку Sch-M.

Подсказки READUNCOMMITTED и NOLOCK для таблиц, измененных операциями вставки, обновления или удаления, указать нельзя. Оптимизатор запросов SQL Server не учитывает подсказки READUNCOMMITTED и NOLOCK в предложении FROM, применяемые к целевой таблице инструкции UPDATE или DELETE.

Поддержка использования подсказок READUNCOMMITTED и NOLOCK в предложении FROM, применяемом к целевой таблице инструкции UPDATE или DELETE, будет удалена в следующей версии SQL Server. Следует избегать использования этих указаний в таком контексте в новой разработке и запланировать изменение приложений, использующих их в настоящий момент.

Минимизировать состязание блокировок во время защиты транзакций от «грязных» чтений незафиксированных изменений данных можно следующими способами.

Дополнительные сведения об уровнях изоляции см. в разделе SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

Если выдается сообщение об ошибке 601 при заданном параметре READUNCOMMITTED, ее следует разрешить так же, как и ошибку взаимоблокировки (сообщение об ошибке 1205), и затем повторить инструкцию.

REPEATABLEREAD
Указывает, что сканирование выполняется с той же семантикой блокировки, что и транзакция, запущенная на уровне изоляции REPEATABLE READ. Дополнительные сведения об уровнях изоляции см. в разделе SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

ROWLOCK
Указывает, что вместо блокировки страниц или таблиц применяются блокировки строк. При указании блокировок строк в транзакциях, выполняемых на уровне изоляции SNAPSHOT, они применяются только в случае, когда подсказка ROWLOCK используется в сочетании с другими табличными подсказками, требующими блокировки, например UPDLOCK или HOLDLOCK. ROWLOCK нельзя использовать с таблицей, имеющей кластеризованный индекс columnstore. В следующем примере в приложении возвращается ошибка 651.

SERIALIZABLE
Равнозначен аргументу HOLDLOCK. Накладывает дополнительные ограничения на совмещаемую блокировку: удерживает ее до завершения транзакции вместо снятия блокировки сразу после того, как таблица или страница данных больше не требуется, независимо от того, завершена ли транзакция. Сканирование выполняется с той же семантикой, что и транзакция, запущенная на уровне изоляции SERIALIZABLE. Дополнительные сведения об уровнях изоляции см. в разделе SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

SNAPSHOT
Область применения: SQL Server 2014 (12.x) и более поздних версий.

Доступ к таблице, оптимизированной для памяти, выполняется с изоляцией SNAPSHOT. SNAPSHOT может использоваться только с таблицами, оптимизированными для памяти (не с дисковыми таблицами), как показано в следующем примере. Дополнительные сведения см. в разделе Введение в таблицы, оптимизированные для памяти.

SPATIAL_WINDOW_MAX_CELLS =
Область применения: SQL Server 2012 (11.x) и более поздних версий.
Указывает максимальное количество ячеек, используемых для тесселяции геометрического или географического объекта. — это число от 1 до 8192.

Этот параметр позволяет выполнять тонкую настройку времени выполнения запроса за счет настройки компромисса между временем выполнения первичного и вторичного фильтра. Чем больше число, тем меньше время выполнения вторичного фильтра и больше время выполнения первичного фильтра, и наоборот. Для получения более плотных пространственных данных большее число должно давать большее время выполнения за счет лучшего приближения с первичным фильтром и сокращения времени выполнения вторичного фильтра. Для получения более разреженных данных меньшее число сократит время выполнения первичного фильтра.

Этот параметр работает и в ручной и в автоматической тесселяции сетки.

TABLOCK
Указывает, что полученная блокировка применяется на уровне таблицы. Тип полученной блокировки зависит от того, какая инструкция выполняется. Например, инструкция SELECT может потребовать совмещаемой блокировки. При указании TABLOCK совмещаемая блокировка применяется ко всей таблице, а не на уровне строк или страниц. Если также указано HOLDLOCK, то блокировка таблицы удерживается до конца транзакции.

Во время импорта данных в кучу с помощью инструкции INSERT INTO SELECT FROM можно включить минимальное ведение журнала и оптимизированную блокировку для инструкции, задав для целевой таблицы указание TABLOCK. Кроме того, для базы данных должна быть задана простая модель восстановления или модель восстановления с неполным протоколированием. Кроме того, подсказка TABLOCK позволяет выполнять параллельные вставки в кучи или кластеризованные индексы columnstore. Дополнительные сведения см. в статье INSERT (Transact-SQL).

При использовании с поставщиком больших наборов строк OPENROWSET для импорта данных в таблицу указание TABLOCK позволяет нескольким клиентам параллельно загружать данные в целевую таблицу с оптимизацией записи в журнал и блокировки. Дополнительные сведения см. в разделе Предварительные условия для минимального протоколирования массового импорта данных.

TABLOCKX
Указывает, что к таблице применяется монопольная блокировка.

UPDLOCK
Указывает, что блокировки обновления применяются и удерживаются до завершения транзакции. UPDLOCK получает блокировки обновления для операций чтения только на уровне строк или страниц. Если UPDLOCK используется в сочетании с TABLOCK или по какой-либо другой причине уже получена блокировка на уровне таблицы, то вместо них будет получена монопольная (X) блокировка.

Если указано UPDLOCK, то указания уровня изоляции READCOMMITTED и READCOMMITTEDLOCK не учитываются. Например, если уровень изоляции в данном сеансе установлен в SERIALIZABLE и в запросе указано (UPDLOCK, READCOMMITTED), то указание READCOMMITTED не учитывается и транзакция будет выполняться на уровне изоляции SERIALIZABLE.

XLOCK
Указывает, что монопольные блокировки применяются и удерживаются до завершения транзакции. Если при этом указан аргумент ROWLOCK, PAGLOCK или TABLOCK, монопольная блокировка применяется к соответствующему уровню гранулярности.

Remarks

Табличные указания пропускаются, если доступ к таблице не предусмотрен планом запроса. Это может быть вызвано тем, что оптимизатор вообще отказался от доступа к таблице или вместо этого получает доступ к индексированному представлению. В последнем случае доступ к индексированному представлению можно предотвратить с помощью подсказки в запросе OPTION (EXPAND VIEWS).

Все подсказки блокировки распространяются на все таблицы и представления, к которым имеет доступ данный план запроса, в том числе в таблицы и представления, на которые ссылается данное представление. Кроме того, SQL Server выполняет соответствующие проверки согласованности блокировок.

Указания блокировки ROWLOCK, UPDLOCK и XLOCK, накладывающие блокировку уровня строки, могут накладывать блокировки на ключи индекса вместо фактических строк данных. Например, если для таблицы имеется некластеризованный индекс, а инструкция SELECT обрабатывается покрывающим индексом с использованием подсказки блокировки, блокировка накладывается на ключ покрывающего индекса вместо строки данных в базовой таблице.

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

SQL Server не разрешает более одного табличного указания из каждой из следующих групп в каждой из таблиц в предложении FROM.

Подсказки отфильтрованного индекса

Оптимизатор запросов не учитывает указание индекса, если в параметрах SET нет требуемых значений для отфильтрованных индексов. Дополнительные сведения см. в разделе CREATE INDEX (Transact-SQL).

Использование NOEXPAND

Аргумент NOEXPAND применяется только для индексированных представлений. Индексированное представление — это представление с созданным на нем уникальным кластеризованным индексом. Если запрос содержит ссылки на столбцы, присутствующие как в индексированном представлении, так и в базовых таблицах, а оптимизатор запросов определяет, что использование индексированного представления является лучшим методом выполнения запроса, то оптимизатор будет использовать индекс представления. Эта функциональная возможность называется сопоставлением индексированного представления. До SQL Server 2016 (13.x); с пакетом обновления 1 (SP1) автоматическое использование индексированного представления оптимизатором запросов поддерживали только определенные выпуски SQL Server. См. сведения о выпусках и поддерживаемых функциях SQL Server 2016, 2017 и 2019 (15.x).

Чтобы оптимизатор запросов учитывал индексированные представления для сопоставления или применял индексированное представление, обращение к которому производится с использованием указания NOEXPAND, нужно задать для следующих параметров SET значение ON.

База данных SQL Azure поддерживает автоматическое использование индексированного представления без указания NOEXPAND.

1 Параметр ARITHABORT неявным образом получает значение ON, когда для ANSI_WARNINGS устанавливается ON. Поэтому менять этот параметр вручную не обязательно.

Кроме того, параметр NUMERIC_ROUNDABORT нужно установить в OFF.

Чтобы оптимизатор запросов использовал индекс для индексированного представления, определите параметр NOEXPAND. Это указание можно использовать только в случае, если представление также названо в запросе. В SQL Server нет указания для принудительного использования определенного индексированного представления в запросе, в котором представление явно не названо в предложении FROM. При этом оптимизатор запросов может использовать индексированные представления, даже если запрос не обращается к ним напрямую. С помощью Компонент SQL Server Database Engine в индексированном представлении автоматически создается статистика только при использовании табличного указания NOEXPAND. Пропуск этой подсказки может привести к предупреждениям об отсутствующей статистике, которые невозможно разрешить, создав статистику вручную. Во время оптимизации запроса Компонент Database Engine будет использовать статистику представления, созданную автоматически или вручную, когда запрос напрямую ссылается на представление и используется указание NOEXPAND.

Использование табличного указания в качестве указания запроса

Табличные указания могут использоваться в качестве указаний запроса с помощью предложения OPTION (TABLE HINT). Табличные указания рекомендуется использовать в качестве подсказок в запросах только в контексте структуры плана. Для нерегламентированных запросов эти указания следует задавать как табличные указания. Дополнительные сведения см. в разделе Указания запросов (Transact-SQL).

Разрешения

Для указаний KEEPIDENTITY, IGNORE_CONSTRAINTS и IGNORE_TRIGGERS требуются разрешения ALTER для таблицы.

Примеры

A. Использование подсказки TABLOCK для указания метода блокировки

В следующем примере показано, как на таблицу Production.Product в базе данных AdventureWorks2012 накладывается совмещаемая блокировка, удерживаемая до завершения инструкции UPDATE.

Б. Использование указания FORCESEEK для указания операции поиска в индексе

В следующем примере показано использование указания FORCESEEK без указания индекса, предписывающее оптимизатору запросов выполнять операцию поиска в индексе для таблицы Sales.SalesOrderDetail в базе данных AdventureWorks2012.

В следующем примере указание FORCESEEK с индексом предписывает оптимизатору запросов выполнить операцию поиска по указанному индексу и столбцу индекса.

Источник