Поделиться через

  • Статья

Табличные подсказки (Transact-SQL)

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

ПредупреждениеВнимание!

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

Область применения:

DELETE

INSERT

SELECT

UPDATE

MERGE

Значок ссылки на разделСинтаксические обозначения в Transact-SQL

Синтаксис

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

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

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

Аргументы

  • WITH ( <табличная_подсказка> ) [ [ , ]...n ]
    За некоторыми исключениями, табличные подсказки поддерживаются только при указании с ключевым словом WITH. Указание круглых скобок обязательно.

    Важное примечаниеВажно!

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

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

    Следующие табличные подсказки можно указывать как с ключевым словом WITH, так и без него: NOLOCK, READUNCOMMITTED, UPDLOCK, REPEATABLEREAD, SERIALIZABLE, READCOMMITTED, FASTFIRSTROW, TABLOCK, TABLOCKX, PAGLOCK, ROWLOCK, NOWAIT, READPAST, XLOCK и NOEXPAND. Если такие табличные подсказки указываются без ключевого слова WITH, подсказки следует задавать отдельно. Например, FROM t WITH (TABLOCK). Если эта подсказка указана с другим параметром, то необходимо указать подсказку с ключевым словом WITH. Например, FROM t WITH (TABLOCK, INDEX(myindex)).

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

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

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

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

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

    ПримечаниеПримечание

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

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

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

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

    Важное примечаниеВажно!

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

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

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

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

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

    Пример использования данной подсказки в инструкции INSERT… SELECT <columns> FROM OPENROWSET(BULK...) см. в разделе Сохранение значений NULL или использование значений по умолчанию при массовом импорте данных.

  • FASTFIRSTROW
    Равнозначен аргументу OPTION (FAST 1). Дополнительные сведения см. в разделе Подсказки в запросах (Transact-SQL).

    Важное примечаниеВажно!

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

  • FORCESEEK [ **(index_value(**index_column_name [ ,... n ] )) ]
    Указывает, что в качестве пути доступа к данным таблиц или представлений оптимизатор запросов использует только операцию поиска в индексе. Начиная с SQL Server 2008 R2 с пакетом обновления 1 (SP1) могут быть указаны также параметры индекса. Подсказка FORCESEEK с параметрами индекса аналогична использованию FORCESEEK с подсказкой INDEX. Но более эффективного контроля над путем доступа, который использует оптимизатор запросов, можно добиться указанием индекса для поиска и столбцов индекса, которые предполагается использовать в операции поиска.

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

    • index_column_name
      Имя столбца индекса, включаемого в операцию поиска. Оптимизатор запросов использует только операции поиска индексов через указанные индексы, используя как минимум указанные столбцы индекса. При необходимости он может подключить дополнительные столбцы. Например, если указан некластеризованный индекс, то оптимизатор может в дополнение к указанным столбцам выбрать использование ключевых столбцов кластеризованного индекса.

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

    Синтаксис

    Пример

    Описание

    Без подсказки индекса или 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.

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

    • Подсказка не может быть указана для удаленного источника данных. Если подсказка FORCESEEK указана вместе с подсказкой индекса, то возвращается ошибка 7377, а если подсказка FORCESEEK используется без подсказки индекса, то ошибка 8180.

    • Если из-за подсказки FORCESEEK план не был найден, возвращается ошибка 8622.

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

    • Подсказка не может быть указана в сочетании с подсказкой INDEX или другой подсказкой FORCESEEK.

    • Должен быть указан хотя бы один столбец, и это должен быть ведущий ключевой столбец.

    • Дополнительные столбцы индекса можно указать, однако ключевые столбцы пропускать нельзя. Например, если указанный индекс содержит ключевые столбцы a, b и c, то правильным будет синтаксис FORCESEEK (MyIndex (a)) и FORCESEEK (MyIndex (a, b). Неправильным будет синтаксис FORCESEEK (MyIndex (c)) и FORCESEEK (MyIndex (a, c).

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

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

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

    • Подсказка предписывает оптимизатору не использовать пространственные индексы и XML-индексы таблиц.

    • Подсказка не может указываться в сочетании с подсказкой FORCESCAN.

    • Для секционированных индексов столбец секционирования, неявным образом добавленный SQL Server, не может быть указан в подсказке FORCESEEK. Дополнительные сведения см. в разделе Дополнительные рекомендации по секционированным индексам.

    ПредупреждениеВнимание!

    Если указывается FORCESEEK с ограничениями параметров, то число планов, которые могут быть учтены оптимизатором, будет больше, чем при указании FORCESEEK без параметров. Это может привести к повышению вероятности возникновения ошибки «Невозможно сформировать план». В будущих версиях внутренние изменения оптимизатора могут привести к увеличению числа этих планов. Дополнительные сведения см. в разделе Использование табличной подсказки FORCESEEK.

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

    Подсказка FORCESCAN может быть указана с подсказкой INDEX или без подсказки 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 проверяет ограничения CHECK и FOREIGN KEY. Если для операции массового импорта задан параметр 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 эта возможность будет удалена. Избегайте использования этой возможности в новых разработках и запланируйте изменение существующих приложений, в которых она применяется.

  • NOWAIT
    Указывает компоненту Database Engine вернуть сообщение сразу после наложения блокировки на таблицу. Аргумент NOWAIT равнозначен указанию SET LOCK_TIMEOUT 0 для конкретной таблицы.

  • 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, могут блокироваться либо при считывании внешних ключей или индексированных представлений, либо при изменении вторичных индексов.

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

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

    • Уровень изоляции транзакций сеанса имеет значение 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. Дополнительные сведения о работе блокировок см. в разделе Совместимость блокировок (компонент Database Engine).

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

    ПримечаниеПримечание

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

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

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

    • Уровень изоляции моментального снимка (SNAPSHOT).

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

    ПримечаниеПримечание

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

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

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

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

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

    Во время импорта данных в кучу с помощью инструкции INSERT INTO <целевая_таблица> SELECT <столбцы> FROM <исходная_таблица> можно включить оптимизированное ведение журнала и блокировки для инструкции, указав для целевой таблицы подсказку TABLOCK. Кроме того, для базы данных должна быть задана простая модель восстановления или модель восстановления с неполным протоколированием. Дополнительные сведения см. в разделе Инструкция 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, NOLOCK, READCOMMITTEDLOCK, ROWLOCK, TABLOCK или TABLOCKX.

  • Подсказки уровня изоляции: HOLDLOCK, NOLOCK, READCOMMITTED, REPEATABLEREAD, SERIALIZABLE.

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

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

USE AdventureWorks2008R2;
GO
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 Enterprise и Developer.

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

ANSI_NULLS

ANSI_WARNINGS

CONCAT_NULL_YIELDS_NULL

ANSI_PADDING

ARITHABORT1

QUOTED_IDENTIFIERS

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

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

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

Дополнительные сведения см. в разделе Разрешение индексов для представлений.

Использование табличной подсказки в качестве подсказки в запросе

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

Разрешения

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

Примеры

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

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

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

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

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

USE AdventureWorks2008R2;
GO
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 с индексом предписывает оптимизатору запросов выполнить операцию поиска по указанному индексу и столбцу индекса.

USE AdventureWorks2008R2; 
GO
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

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

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

USE AdventureWorks2008R2; 
GO
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);