Указания таблиц (Transact-SQL)

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azureбазе данных SQL в Microsoft Fabric

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

Относится к:

• DELETE
• INSERT
• SELECT
• UPDATE
• MERGE

Соглашения о синтаксисе Transact-SQL

Синтаксис

WITH  ( <table_hint> [ [ , ] ...n ] )

<table_hint> ::=
{ NOEXPAND
  | INDEX ( <index_value> [ , ...n ] ) | INDEX = ( <index_value> )
  | FORCESEEK [ ( <index_value> ( <index_column_name> [ , ... ] ) ) ]
  | FORCESCAN
  | HOLDLOCK
  | NOLOCK
  | NOWAIT
  | PAGLOCK
  | READCOMMITTED
  | READCOMMITTEDLOCK
  | READPAST
  | READUNCOMMITTED
  | REPEATABLEREAD
  | ROWLOCK
  | SERIALIZABLE
  | SNAPSHOT
  | SPATIAL_WINDOW_MAX_CELLS = <integer_value>
  | TABLOCK
  | TABLOCKX
  | UPDLOCK
  | XLOCK
}

<table_hint_limited> ::=
{
    KEEPIDENTITY
  | KEEPDEFAULTS
  | HOLDLOCK
  | IGNORE_CONSTRAINTS
  | IGNORE_TRIGGERS
  | NOLOCK
  | NOWAIT
  | PAGLOCK
  | READCOMMITTED
  | READCOMMITTEDLOCK
  | READPAST
  | REPEATABLEREAD
  | ROWLOCK
  | SERIALIZABLE
  | SNAPSHOT
  | TABLOCK
  | TABLOCKX
  | UPDLOCK
  | XLOCK
}

Аргументы

WITH ( <table_hint> ) [ [ , ] ...n ]

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

Подсказки в следующей таблице можно использовать как с ключевым словом WITH, так и без него: NOLOCK, READUNCOMMITTED, UPDLOCK, REPEATABLEREAD, SERIALIZABLE, READCOMMITTED, TABLOCK, TABLOCKX, PAGLOCK, ROWLOCK, NOWAIT, READPAST, XLOCK, SNAPSHOT и NOEXPAND. Если эти указания таблицы указаны без ключевого WITH слова, указания должны быть указаны только. Например:

FROM t (TABLOCK)

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

FROM t WITH (TABLOCK, INDEX(myindex))

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

NOEXPAND

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

INDEX ( <index_value> [ , ...n ] ) | INDEX = ( <index_value> )

Синтаксис INDEX() задает имена или идентификаторы одного или нескольких индексов, которые будут использоваться оптимизатором запросов при обработке инструкции. Альтернативный синтаксис INDEX = определяет отдельное значение индекса. Для каждой таблицы можно задать только одно указание индекса.

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

Если в одном списке подсказок используются несколько индексов, дубликаты игнорируются, а остальные перечисленные индексы используются для извлечения строк таблицы. Порядок индексов в указании индекса имеет значение. Несколько указаний индекса также принудительно выполняют операции И с индексами, и оптимизатор запросов применяет столько условий, сколько возможно для каждого из индексов, к которым он получает доступ. Если коллекция намеченных индексов не включает все столбцы, на которые ссылается запрос, выполняется получение оставшихся столбцов после того, как SQL Server ядро СУБД извлекает все индексированные столбцы.

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

KEEPIDENTITY

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

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

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

Сведения о проверке значения удостоверения для таблицы см. в DBCC CHECKIDENT.

KEEPDEFAULTS

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

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

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

FORCESEEK [ ( index_value ( <>index_column_name<> [ , ...n ] ) ]

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

• index_value

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

• index_column_name

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

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

Синтаксис	Пример	Description
Без индекса или INDEX указания	FROM dbo.MyTable WITH (FORCESEEK)	Оптимизатор запросов использует только операции поиска в индексе для доступа к таблицам или представлениям через любой подходящий индекс.
В сочетании с указанием INDEX	FROM dbo.MyTable WITH (FORCESEEK, INDEX (MyIndex))	Оптимизатор запросов будет использовать при доступе к таблице или представлению через указанный индекс только операции поиска по индексу.
Параметризация посредством указания индекса и столбцов индекса	FROM dbo.MyTable WITH (FORCESEEK (MyIndex (col1, col2, col3)))	Оптимизатор запросов будет использовать при выполнении поиска по указанной таблице или представлению индексу как минимум указанные столбцы индекса.

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

• Указание может задаваться как табличное указание или как указание запроса. Дополнительные сведения о подсказках запросов см. в подсказках запросов (Transact-SQL).
• Чтобы применить FORCESEEK индексированное представление, NOEXPAND необходимо также указать указание.
• Указание может применяться не более одного раза для каждой таблицы или представления.
• Указание невозможно указать для удаленного источника данных. Ошибка 7377 возвращается при FORCESEEK указании индекса и возвращается ошибка 8180 при FORCESEEK использовании без указания индекса.
• Если FORCESEEK не удается найти планы, возвращается ошибка 8622.

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

• Указание не может быть указано для таблицы, которая является целевым объектом инструкции INSERT, UPDATEили DELETE.
• Указание не может быть указано в сочетании с указанием INDEX или другим FORCESEEK указанием.
• Должен быть указан хотя бы один столбец, который должен быть ведущим ключевым столбцом.
• Однако можно указать дополнительные столбцы индекса, но нельзя пропускать ключевые столбцы. Например, если указанный индекс содержит ключевые столбцы a, b и c, то правильный синтаксис должен содержать FORCESEEK (MyIndex (a)) и FORCESEEK (MyIndex (a, b). Неправильный синтаксис может содержать FORCESEEK (MyIndex (c)) и FORCESEEK (MyIndex (a, c).
• Порядок следования имен столбцов, заданный в указании, должен соответствовать порядку столбцов в упоминаемом индексе.
• Столбцы, которые не указаны в определении ключа индекса, не могут быть указаны. Например, в некластеризованном индексе могут быть указаны только определенные в индексе ключевые столбцы. Кластеризованные ключевые столбцы, автоматически включенные в индекс, не могут быть указаны, но могут использоваться оптимизатором.
• Индекс columnstore, оптимизированный для памяти xVelocity, нельзя указать в качестве параметра индекса. Возвращается ошибка 366.
• Изменение определения индекса (например, путем добавления или удаления столбцов) может потребовать изменения запросов, ссылающихся на этот индекс.
• Указание запрещает оптимизатору использовать пространственные индексы и XML-индексы таблицы.
• Указание не может быть указано в сочетании с указанием FORCESCAN .
• Для секционированных индексов столбец секционирования, неявно добавленный SQL Server, не может быть указан в указании FORCESEEK .

FORCESCAN

Область применения: SQL Server 2008 R2 (10.50.x) с пакетом обновления 1 и более поздних версий

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

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

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

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

• Указание не может быть указано для таблицы, которая является целевым объектом инструкции INSERT, UPDATEили DELETE.
• Указание не может использоваться с несколькими указаниями индекса.
• Указание запрещает оптимизатору запросов рассматривать любые пространственные или XML-индексы в таблице.
• Указание невозможно указать для удаленного источника данных.
• Указание не может быть указано в сочетании с указанием FORCESEEK .

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 далее в этой статье.

NOWAIT

Указывает, что ядро СУБД должно вернуть сообщение сразу после обнаружения блокировки таблицы. NOWAIT эквивалентен указанию SET LOCK_TIMEOUT 0 для определенной таблицы. Указание NOWAIT не работает, когда также включена подсказка TABLOCK . Чтобы завершить запрос, не ожидая при использовании TABLOCK указания, предусловите запрос SET LOCK_TIMEOUT 0; вместо этого.

PAGLOCK

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

READCOMMITTED

Указывает, что операции чтения соответствуют правилам уровня изоляции READ COMMITTED с помощью блокировки или управления версиями строк. Если параметр базы данных READ_COMMITTED_SNAPSHOTOFF, ядро СУБД получает общие блокировки при чтении и освобождает эти блокировки при завершении операции чтения. Если параметр базы данных READ_COMMITTED_SNAPSHOTON, ядро СУБД не получает блокировки и использует управление версиями строк. Дополнительные сведения об уровнях изоляции см. в разделе SET TRANSACTION ISOLATION LEVEL.

READCOMMITTEDLOCK

Указывает, что операции чтения соответствуют правилам уровня изоляции READ COMMITTED с помощью блокировки. Ядро СУБД получает общие блокировки при чтении данных и освобождает эти блокировки при завершении операции чтения независимо от параметра READ_COMMITTED_SNAPSHOT базы данных. Дополнительные сведения об уровнях изоляции см. в разделе SET TRANSACTION ISOLATION LEVEL. Это указание нельзя указать в целевой таблице инструкции INSERT; Возвращается ошибка 4140.

READPAST

Указывает, что ядро СУБД не должно считывать строки и страницы, заблокированные другими транзакциями. При READPAST указании блокировки на уровне строк пропускаются, но блокировки на уровне страницы не пропускаются. Ядро СУБД будет пропускать строки вместо блокировки текущей транзакции до тех пор, пока блокировки не будут сняты. Например, предположим, что в таблице 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. При указании в инструкции UPDATEREADPAST применяется только при чтении данных, чтобы определить, какие записи необходимо обновить, независимо от того, где он указан в инструкции. READPAST нельзя указать для таблиц в предложении INTO оператора INSERT. Операции обновления или удаления, использующие READPAST, могут блокироваться при чтении внешних ключей или индексированных представлений или при изменении вторичных индексов.

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

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

• Уровень изоляции транзакций сеанса READ COMMITTED.
• Указание READCOMMITTED таблицы также указывается в запросе.

Чтобы указать 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.

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

• Уровень изоляции READ COMMITTED с параметром READ_COMMITTED_SNAPSHOT базы данных ON.
• Уровень SNAPSHOT изоляции.

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

REPEATABLEREAD

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

ROWLOCK

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

UPDATE [dbo].[FactResellerSalesXL_CCI] WITH (ROWLOCK)
    SET UnitPrice = 50
WHERE ProductKey = 150;

SERIALIZABLE

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

МОМЕНТАЛЬНЫЙ СНИМОК

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

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

SELECT *
FROM dbo.Customers AS c WITH (SNAPSHOT)
     LEFT OUTER JOIN dbo.[Order History] AS oh
         ON c.customer_id = oh.customer_id;

SPATIAL_WINDOW_MAX_CELLS = <integer_value>

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

Указывает максимальное количество ячеек, используемых для тесселяции геометрического или географического объекта. <integer_value> может содержать любое число в диапазоне от 1 до 8192.

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

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

TABLOCK

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

При импорте данных в кучу с помощью INSERT INTO <target_table> SELECT <columns> FROM <source_table> инструкции можно включить минимальное ведение журнала и оптимальную блокировку для инструкции, указав TABLOCK указание целевой таблицы. Кроме того, для базы данных должна быть задана простая модель восстановления или модель восстановления с неполным протоколированием. Указание TABLOCK также позволяет параллельным вставкам в кучу или кластеризованные индексы columnstore. Дополнительные сведения см. в разделе INSERT.

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

TABLOCKX

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

UPDLOCK

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

При UPDLOCK указании READCOMMITTEDREADCOMMITTEDLOCK подсказки уровня изоляции игнорируются. Например, если задан SERIALIZABLE уровень изоляции сеанса и указан запрос (UPDLOCK, READCOMMITTED), READCOMMITTED указание игнорируется, а транзакция выполняется с помощью SERIALIZABLE уровня изоляции.

XLOCK

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

Замечания

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

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

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

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

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

• Детализированные указания: PAGLOCK, , NOLOCKREADCOMMITTEDLOCK, ROWLOCK, TABLOCKили TABLOCKX.
• Указания уровня изоляции: HOLDLOCK, NOLOCK, READCOMMITTED, REPEATABLEREAD. SERIALIZABLE

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

Отфильтрованный индекс можно использовать в качестве указания таблицы, но заставляет оптимизатор запросов создавать ошибку 8622, если она не охватывает все строки, которые выбирает запрос. Далее приводится пример недопустимого указания отфильтрованного индекса. В примере создается отфильтрованный индекс FIBillOfMaterialsWithComponentID, а затем он используется в качестве указания индекса для инструкции SELECT. Предикат отфильтрованного индекса содержит строки данных для ComponentID со значениями 533, 324 и 753. Предикат запроса также содержит строки данных для componentIDs 533, 324 и 753, но расширяет результирующий набор, чтобы включить компоненты 855 и 924, которые не входят в фильтрованный индекс. Поэтому оптимизатор запросов не может использовать отфильтрованный индекс и создает ошибку 8622. Дополнительные сведения см. в статье Создание отфильтрованных индексов.

IF EXISTS (SELECT name FROM sys.indexes
           WHERE name = N'FIBillOfMaterialsWithComponentID'
                 AND object_id = OBJECT_ID(N'Production.BillOfMaterials'))
    DROP INDEX FIBillOfMaterialsWithComponentID
        ON Production.BillOfMaterials;
GO

CREATE NONCLUSTERED INDEX [FIBillOfMaterialsWithComponentID]
    ON Production.BillOfMaterials(ComponentID, StartDate, EndDate)
    WHERE ComponentID IN (533, 324, 753);
GO

SELECT StartDate, ComponentID
FROM Production.BillOfMaterials
WITH (INDEX (FIBillOfMaterialsWithComponentID))
WHERE ComponentID IN (533, 324, 753, 855, 924);
GO

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

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

NOEXPAND применяется только к индексированных представлениям. Индексированное представление — это представление с созданным на нем уникальным кластеризованным индексом. Если запрос содержит ссылки на столбцы, присутствующие как в индексированном представлении, так и в базовых таблицах, а оптимизатор запросов определяет, что использование индексированного представления является лучшим методом выполнения запроса, оптимизатор будет использовать индекс представления. Эта функциональная возможность называется сопоставлением индексированного представления. Прежде чем SQL Server 2016 (13.x) с пакетом обновления 1 (SP1) автоматическое использование индексированного представления оптимизатором запросов поддерживается только в определенных выпусках SQL Server. В SQL Server 2016 (13.x) с пакетом обновления 1 и более поздних версий все выпуски поддерживают автоматическое использование индексированного представления. База данных SQL Azure и Управляемый экземпляр SQL Azure также поддерживают автоматическое использование индексированных представлений без указания NOEXPAND указания.

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

Список функций, поддерживаемых выпусками SQL Server в Windows, см. в следующих статье:

• Выпуски и поддерживаемые функции SQL Server 2022
• Выпуски и поддерживаемые функции SQL Server 2019
• Выпуски SQL Server 2017 и поддерживаемые функции
• Выпуски и поддерживаемые функции SQL Server 2016

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

• ANSI_NULLS
• ANSI_PADDING
• ANSI_WARNINGS
• ARITHABORT ¹
• CONCAT_NULL_YIELDS_NULL
• QUOTED_IDENTIFIER

¹ARITHABORT неявно устанавливается ON, если ANSI_WARNINGS задано значение ON. Поэтому вам не нужно вручную настраивать этот параметр.

Кроме того, параметр NUMERIC_ROUNDABORT должен иметь значение OFF.

Чтобы принудительно оптимизатор запросов использовал индекс для индексированного представления, укажите NOEXPAND этот параметр. Это указание можно использовать только в случае, если представление также названо в запросе. SQL Server не предоставляет указания на принудительное использование определенного индексированного представления в запросе, который не называет представление непосредственно в предложении FROM. Однако оптимизатор запросов рассматривает использование индексированных представлений, даже если они не ссылаются непосредственно в запросе. Ядро СУБД SQL Server автоматически создает статистику для индексированного представления при использовании указания таблицы NOEXPAND. Опущение этого указания может привести к предупреждениям плана выполнения о отсутствии статистики, которая не может быть разрешена путем создания статистики вручную.

Во время оптимизации запросов ядро СУБД использует статистику просмотра, созданную автоматически или вручную, когда запрос ссылается непосредственно на представление и используется подсказкаNOEXPAND.

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

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

Разрешения

Указания KEEPIDENTITY, IGNORE_CONSTRAINTSи IGNORE_TRIGGERS требуют ALTER разрешений в таблице.

Примеры

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

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

UPDATE Production.Product WITH (TABLOCK)
    SET ListPrice = ListPrice * 1.10
WHERE ProductNumber LIKE 'BK-%';
GO

B. Используйте указание FORCESEEK, чтобы указать операцию поиска индекса

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

SELECT *
FROM Sales.SalesOrderHeader AS h
     INNER JOIN Sales.SalesOrderDetail AS d WITH (FORCESEEK)
         ON h.SalesOrderID = d.SalesOrderID
WHERE h.TotalDue > 100
      AND (d.OrderQty > 5
           OR d.LineTotal < 1000.00);
GO

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

SELECT h.SalesOrderID,
       h.TotalDue,
       d.OrderQty
FROM Sales.SalesOrderHeader AS h
     INNER JOIN Sales.SalesOrderDetail AS d
         WITH (FORCESEEK (PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID (SalesOrderID)))
         ON h.SalesOrderID = d.SalesOrderID
WHERE h.TotalDue > 100
      AND (d.OrderQty > 5
           OR d.LineTotal < 1000.00);
GO

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

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

SELECT h.SalesOrderID,
       h.TotalDue,
       d.OrderQty
FROM Sales.SalesOrderHeader AS h
     INNER JOIN Sales.SalesOrderDetail AS d WITH (FORCESCAN)
         ON h.SalesOrderID = d.SalesOrderID
WHERE h.TotalDue > 100
      AND (d.OrderQty > 5
           OR d.LineTotal < 1000.00);

Связанный контент

• OPENROWSET (Transact-SQL)
• Подсказки (Transact-SQL)
• подсказки запросов (Transact-SQL)