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

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

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

• 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).

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

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

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

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

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

Разрешения

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

Примеры

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

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

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

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

См. также

Справочник

Основные понятия