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

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

Указания таблиц используются для переопределения поведения оптимизатора запросов по умолчанию во время инструкции языка обработки данных (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 применяется только к индексированных представлениям. Дополнительные сведения см. в разделе Использование 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 (Transact-SQL).

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_SNAPSHOT базы данных отключен, ядро СУБД получает общие блокировки при чтении и освобождает эти блокировки при завершении операции чтения. Если параметр READ_COMMITTED_SNAPSHOT базы данных включен, ядро СУБД не получает блокировки и использует управление версиями строк. Дополнительные сведения об уровнях изоляции см. в статье SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

READCOMMITTEDLOCK

Указывает, что операции чтения соответствуют правилам для уровня изоляции READ COMMITTED путем использования блокировки. Ядро СУБД получает общие блокировки при чтении данных и освобождает эти блокировки при завершении операции чтения независимо от параметра READ_COMMITTED_SNAPSHOT базы данных. Дополнительные сведения об уровнях изоляции см. в статье SET TRANSACTION ISOLATION LEVEL (Transact-SQL). Это указание нельзя указать в целевой таблице инструкции 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. При указании в инструкции READPAST UPDATE применяется только при чтении данных, чтобы определить, какие записи следует обновлять, независимо от того, где он указан в инструкции. 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 до того, как она изменяет данные схемы. При попытке получить блокировку Sch-S блокируются любые одновременные запросы, включая запущенные с READUNCOMMITTED помощью или NOLOCK подсказки. И наоборот, запрос, удерживающий блокировку Sch-S, блокирует параллельную транзакцию, которая пытается получить блокировку Sch-M.

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

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

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

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

REPEATABLEREAD

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

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 (Transact-SQL).

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

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

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

SELECT *
FROM dbo.Customers AS c WITH (SNAPSHOT)
LEFT 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 (Transact-SQL).

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

TABLOCKX

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

UPDLOCK

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

При UPDLOCK указании READCOMMITTED READCOMMITTEDLOCK подсказки уровня изоляции игнорируются. Например, если задан 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. Дополнительные сведения см. в разделе Create Filtered Indexes.

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 (Transact-SQL).

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

NOEXPAND применяется только к индексированных представлениям. Индексированное представление — это представление с созданным на нем уникальным кластеризованным индексом. Если запрос содержит ссылки на столбцы, присутствующие как в индексированном представлении, так и в базовых таблицах, а оптимизатор запросов определяет, что использование индексированного представления является лучшим методом выполнения запроса, оптимизатор будет использовать индекс представления. Эта функциональная возможность называется сопоставлением индексированного представления. До SQL Server 2016 (13.x) с пакетом обновления 1 (SP1) автоматическое использование индексированного представления оптимизатором запросов поддерживается только в определенных выпусках SQL Server. Так как все выпуски поддерживают автоматическое использование индексированного представления. База данных 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). Табличные указания рекомендуется использовать в качестве подсказок в запросах только в контексте структуры плана. Для нерегламентированных запросов укажите эти подсказки только в виде табличных подсказок. Дополнительные сведения см. в разделе Указания запросов (Transact-SQL).

Разрешения

Для указаний 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

C. Использование указания 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) — запросы