Статья
09/28/2010

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

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

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

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

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

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

Синтаксис

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

<table_hint> ::= 
[ NOEXPAND ] { 
    INDEX ( index_value [ ,...n ] ) | INDEX = ( index_value )
  | FASTFIRSTROW 
  | FORCESEEK
  | 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 
  | NOWAIT
  | PAGLOCK 
  | READCOMMITTED 
  | READCOMMITTEDLOCK 
  | READPAST 
  | REPEATABLEREAD 
  | ROWLOCK 
  | SERIALIZABLE 
  | TABLOCK 
  | TABLOCKX 
  | UPDLOCK 
  | XLOCK 
}

Аргументы

WITH ( <table_hint> ) [ [ , ]...n ]
Табличные подсказки, за некоторыми исключениями, поддерживаются в предложении FROM только в случае, когда подсказки указаны с ключевым словом 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 (TABLOCK)

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

FROM t WITH (TABLOCK, INDEX(myindex))

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Табличная подсказка FORCESEEK может использоваться с операциями поиска как в кластеризованном, так и в некластеризованном индексе. Ее можно указывать для каждой таблицы или представления в предложении FROM инструкции SELECT и в предложении FROM <table_source> инструкций UPDATE, MERGE или DELETE.

Табличная подсказка FORCESEEK может использоваться как совместно, так и без подсказки индекса. При наличии подсказки индекса оптимизатор запросов ищет пути доступа только в рамках указанного индекса. Если из-за подсказки FORCESEEK план не был найден, возвращается ошибка 8622. Дополнительные сведения см. в разделе Использование табличной подсказки 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 эта возможность будет удалена. Избегайте использования этой возможности в новых разработках и запланируйте изменение существующих приложений, в которых она применяется.

Для инструкций 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).

READCOMMITTEDLOCK
Указывает, что операции чтения соответствуют правилам для уровня изоляции READ COMMITTED путем использования блокировки. Компонент Database Engine накладывает совмещаемые блокировки по мере чтения данных и снимает их после завершения операции чтения вне зависимости от значения параметра базы данных READ_COMMITTED_SNAPSHOT. Дополнительные сведения об уровнях изоляции см. в разделе SET TRANSACTION ISOLATION LEVEL (Transact-SQL).
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.

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

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

Поддержка использования подсказок 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
Указывает, что наложенная на таблицу совмещаемая блокировка удерживается до окончания инструкции. Если при этом указан аргумент HOLDLOCK, совмещаемая блокировка таблицы удерживается до конца транзакции.

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

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

Замечания

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

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

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

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

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

Подсказки степени гранулярности: PAGLOCK, NOLOCK, ROWLOCK, TABLOCK или TABLOCKX.
Подсказки уровня изоляции: HOLDLOCK, NOLOCK, READCOMMITTED, REPEATABLEREAD, SERIALIZABLE.

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

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

USE AdventureWorks;
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 AdventureWorks;
GO
UPDATE Production.Product
WITH (TABLOCK)
SET ListPrice = ListPrice * 1.10
WHERE ProductNumber LIKE 'BK-%';
GO

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

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

USE AdventureWorks;
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