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

Изменения: 12 декабря 2006 г.

Указывает, что оптимизатор запросов использует просмотр таблицы, один или более индексов или метод блокировки для данной таблицы или представления и для данной инструкции SELECT, INSERT, UPDATE или DELETE. Оптимизатор запросов в общем случае может применять наилучший метод оптимизации без указания подсказок, но не обязательно.

Важно!
Так как оптимизатор запросов SQL Server 2005 обычно выбирает наилучший план выполнения запроса, рекомендуется использовать подсказки, включая <table_hint>, только в качестве последнего средства для опытных разработчиков и администраторов баз данных.

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

DELETE

INSERT

SELECT

UPDATE

Синтаксические обозначения в Transact-SQL

Синтаксис

<table_hint> ::= 
[ NOEXPAND ] { 
    INDEX ( index_val [ ,...n ] )
  | FASTFIRSTROW 
  | 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 
} 

Аргументы

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

• INDEX ( index_val [ ,... n ] )
  Указывает имя или идентификатор индексов, используемых при обработке инструкции оптимизатором запросов. Для каждой таблицы можно указать только одну подсказку индекса.

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

  Альтернативный синтаксис «INDEX =» позволяет задать отдельную подсказку индекса. Это поддерживается исключительно для обратной совместимости.

  Если в отдельном списке подсказок используются несколько индексов, дубликаты пропускаются, а остальные из перечисленных индексов используются для получения строк из таблицы. Порядок индексов в подсказке индекса имеет значение. Подсказка нескольких индексов также приводит к выполнению операции AND над индексами, и оптимизатор запросов применяет столько условий, сколько возможно для каждого из индексов, к которым он получает доступ. Если коллекция индексов с подсказками неполна, после получения всех индексированных столбцов компонент SQL Server 2005 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). Дополнительные сведения см. в разделе об аргументе FAST в предложении OPTION инструкции SELECT.

• 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 см. далее в этом разделе.

• NOWAIT
  Указывает компоненту SQL Server 2005 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 не считывает строки и страницы, заблокированные другими транзакциями. Зачастую это справедливо и для страниц. Компонент Database Engine пропускает строки и страницы вместо блокировки текущей транзакции, до тех пор, пока блокировки не будут сняты. Аргумент READPAST можно указывать только в транзакциях, выполняемых на уровнях изоляции READ COMMITTED или REPEATABLE READ. При указании подсказки READPAST в транзакциях, выполняемых с уровнем изоляции SNAPSHOT, она должна использоваться в сочетании с другими табличными подсказками, требующими блокировки, например UPDLOCK или HOLDLOCK. Если указан аргумент READPAST, блокировки уровня строк и уровня страниц будут пропускаться. Аргумент READPAST можно задать для любой таблицы, к которой обращается инструкция UPDATE или DELETE, и к любой таблице, на которую ссылается предложение FROM. Если аргумент READPAST задан в инструкции UPDATE, он применяется только при считывании данных для идентификации подлежащих обновлению записей, вне зависимости от того, где он указан в инструкции. Аргумент READPAST для таблиц из предложения INTO инструкции INSERT задать нельзя.

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

  Например: предположим, что в таблице T1 есть один целочисленный столбец со значениями 1, 2, 3, 4, 5. Если транзакция A изменит значение 3 на 8, но еще не будет подтверждена, инструкция SELECT * FROM T1 (READPAST) передаст значения 1, 2, 4, 5. Аргумент READPAST главным образом используется для снижения конфликта блокировок при реализации рабочей очереди, использующей таблицу SQL Server. Средство чтения очереди, использующее аргумент READPAST, пропускает прошлые записи очереди, заблокированные другими транзакциями, до следующей доступной записи очереди, не дожидаясь, пока другие транзакции снимут свои блокировки.

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

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

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

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

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

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

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

Замечания

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

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

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

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

FROM t (FASTFIRSTROW)

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

FROM t WITH (FASTFIRSTROW, INDEX(myindex))

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

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

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

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

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

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

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

Подсказка NOEXPAND применяется только для индексированных представлений. Индексированное представление — это представление с созданным на нем уникальным кластеризованным индексом. Если запрос содержит ссылки на столбцы, присутствующие как в индексированном представлении, так и в базовых таблицах, а оптимизатор запросов определяет, что использование индексированного представления является лучшим методом выполнения запроса, оптимизатор будет использовать индекс представления. Эта функция называется согласованием индексированного представления и поддерживается только в выпусках SQL Server 2005 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 2005 не предоставляет подсказку, которая могла бы заставить программу использовать в запросе конкретное индексированное представление, имя которого не задано непосредственно в предложении FROM данного запроса; однако оптимизатор запросов выбирает использование индексированных представлений, даже если запрос не обращается к ним напрямую.

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

Разрешения

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

Примеры

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

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

См. также

Справочник

OPENROWSET (Transact-SQL)
Подсказки (Transact-SQL)

Другие ресурсы

Подсказки блокировок
Разрешение представлений

Справка и поддержка

Получение помощи по SQL Server 2005

Журнал изменений

Версия	Журнал
12 декабря 2006 г.	Измененное содержимое Разъяснен тип блокировки, накладываемый подсказкой TABLOCK. В описание подсказки IGNORE_CONSTRAINTS внесены изменения для указания того, что она приводит к пропуску ограничений CHECK и FOREIGN KEY.
14 апреля 2006 г.	Новое содержимое Добавлены сведения об использовании подсказок PAGLOCK, READPAST и ROWLOCK в транзакциях, выполняемых с уровнем изоляции SNAPSHOT.
5 декабря 2005 г.	Измененное содержимое Обновлены сведения о подсказке блокировки READUNCOMMITTED.