Указания таблиц (Transact-SQL)
Область применения: SQL Server
База данных SQL Azure Управляемый экземпляр SQL Azure
Указания таблиц используются для переопределения поведения оптимизатора запросов по умолчанию во время инструкции языка обработки данных (DML). Можно указать метод блокировки, один или несколько индексов, операцию обработки запросов, например сканирование таблицы или поиск индекса или другие параметры. Табличные указания задаются в предложении FROM инструкции DML и относятся только к таблицам и представлениям, на которые ссылается это предложение.
Внимание
Так как оптимизатор запросов SQL Server обычно выбирает оптимальный план выполнения для запроса, мы рекомендуем использовать указания только в качестве последнего средства опытными разработчиками и администраторами баз данных.
Относится к:
Соглашения о синтаксисе Transact-SQL
Синтаксис
WITH ( <table_hint> [ [ , ] ...n ] )
<table_hint> ::=
{ NOEXPAND
| INDEX ( <index_value> [ , ...n ] ) | INDEX = ( <index_value> )
| FORCESEEK [ ( <index_value> ( <index_column_name> [ , ... ] ) ) ]
| FORCESCAN
| HOLDLOCK
| NOLOCK
| NOWAIT
| PAGLOCK
| READCOMMITTED
| READCOMMITTEDLOCK
| READPAST
| READUNCOMMITTED
| REPEATABLEREAD
| ROWLOCK
| SERIALIZABLE
| SNAPSHOT
| SPATIAL_WINDOW_MAX_CELLS = <integer_value>
| TABLOCK
| TABLOCKX
| UPDLOCK
| XLOCK
}
<table_hint_limited> ::=
{
KEEPIDENTITY
| KEEPDEFAULTS
| HOLDLOCK
| IGNORE_CONSTRAINTS
| IGNORE_TRIGGERS
| NOLOCK
| NOWAIT
| PAGLOCK
| READCOMMITTED
| READCOMMITTEDLOCK
| READPAST
| REPEATABLEREAD
| ROWLOCK
| SERIALIZABLE
| SNAPSHOT
| TABLOCK
| TABLOCKX
| UPDLOCK
| XLOCK
}
Примечание.
Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.
Аргументы
WITH ( <table_hint> ) [ [ , ] ...n ]
Табличные указания, за некоторыми исключениями, поддерживаются в предложении FROM только в случае, если они задаются с ключевым словом WITH. Табличные указания также необходимо заключать в скобки.
Внимание
Опущение ключевого слова WITH является нерекомендуемой функцией: эта функция будет удалена в будущей версии SQL Server. Избегайте использования этого компонента в новых разработках и запланируйте изменение существующих приложений, в которых он применяется.
Подсказки в следующей таблице можно использовать как с ключевым словом WITH
, так и без него: NOLOCK
, READUNCOMMITTED
, UPDLOCK
, REPEATABLEREAD
, SERIALIZABLE
, READCOMMITTED
, TABLOCK
, TABLOCKX
, PAGLOCK
, ROWLOCK
, NOWAIT
, READPAST
, XLOCK
, SNAPSHOT
и NOEXPAND
. Если эти указания таблицы указаны без ключевого WITH
слова, указания должны быть указаны только. Например:
FROM t (TABLOCK)
Если подсказка указана с другим параметром, ее необходимо указывать с ключевым словом WITH:
FROM t WITH (TABLOCK, INDEX(myindex))
Между табличными подсказками рекомендуется ставить запятые.
Внимание
Разделяя указания по пробелам, а не запятыми, является устаревшей функцией: эта функция будет удалена в будущей версии SQL Server. Избегайте использования этого компонента в новых разработках и запланируйте изменение существующих приложений, в которых он применяется.
NOEXPAND
Указывает, что индексированные представления не расширяются для доступа к базовым таблицам при обработке запроса оптимизатором запросов. Оптимизатор запросов обрабатывает представление так же, как и таблицу с кластеризованным индексом. NOEXPAND
применяется только к индексированных представлениям. Дополнительные сведения см. в разделе Использование NOEXPAND.
INDEX ( <index_value> [ , ...n ] ) | INDEX = ( <index_value> )
Синтаксис INDEX()
задает имена или идентификаторы одного или нескольких индексов, которые будут использоваться оптимизатором запросов при обработке инструкции. Альтернативный синтаксис INDEX =
определяет отдельное значение индекса. Для каждой таблицы можно задать только одно указание индекса.
Если кластеризованный индекс существует, INDEX(0)
принудительно выполняет кластеризованную проверку индекса и INDEX(1)
принудительно выполняет кластеризованную проверку или поиск индекса. Если кластеризованный индекс не существует, INDEX(0)
выполняет проверку таблицы и INDEX(1)
интерпретируется как ошибка.
Если в одном списке подсказок используются несколько индексов, дубликаты игнорируются, а остальные перечисленные индексы используются для извлечения строк таблицы. Порядок индексов в указании индекса имеет значение. Несколько указаний индекса также принудительно выполняют операции И с индексами, и оптимизатор запросов применяет столько условий, сколько возможно для каждого из индексов, к которым он получает доступ. Если коллекция намеченных индексов не включает все столбцы, на которые ссылается запрос, выполняется получение оставшихся столбцов после того, как SQL Server ядро СУБД извлекает все индексированные столбцы.
Примечание.
Если указание индекса, ссылающееся на несколько индексов, используется в таблице фактов в соединении типа «звезда», оптимизатор не учитывает индекс и возвращает предупреждение. Кроме того, выполнение операции ИЛИ с индексами также не разрешено для таблицы с заданным указанием индекса.
Максимальное число индексов в табличном указании равно 250 некластеризованным индексам.
KEEPIDENTITY
Применимо только в инструкции INSERT, если параметр BULK используется с OPENROWSET.
Указывает, что значение или значения идентификаторов в файле импортированных данных будут использоваться для столбца идентификаторов. Если параметр KEEPIDENTITY не указан, значения удостоверений для этого столбца проверяются, но не импортируются, а оптимизатор запросов автоматически назначает уникальные значения на основе значений начального и добавочного значения, указанные во время создания таблицы.
Внимание
Если файл данных не содержит значений столбца идентификаторов таблицы или представления, а столбец идентификаторов не является последним в таблице, этот столбец необходимо пропустить. Дополнительные сведения см. в статье Использование файла форматирования для пропуска поля данных (SQL Server). Если столбец идентификаторов успешно пропущен, то оптимизатор запросов автоматически назначает уникальные значения для столбца идентификаторов в импортируемые строки таблицы.
Пример использования этого указания в инструкции INSERT ... SELECT * FROM OPENROWSET(BULK...)
см. в статье Сохранение значений идентификаторов при массовом импорте данных (SQL Server).
Дополнительные сведения о проверке идентифицирующего значения для таблицы см. в статье DBCC CHECKIDENT (Transact-SQL).
KEEPDEFAULTS
Применимо только в инструкции INSERT, если параметр BULK используется с OPENROWSET.
Указывает на вставку установленного по умолчанию значения столбца таблицы, если таковое имеется, вместо значения NULL, применяемого в случае, когда запись данных не содержит значения для этого столбца.
Пример использования этого указания в инструкции INSERT… Дополнительные сведения об инструкции SELECT * FROM OPENROWSET(BULK...) см. в разделе Сохранение значений NULL или использование значений по умолчанию при массовом импорте данных (SQL Server).
FORCESEEK [ ( index_value ( <<index_column_name>> [ , ...n ] ) ]
Указывает, что оптимизатор запросов использует только операцию поиска индекса в качестве пути доступа к данным в таблице или представлении.
Примечание.
Начиная с SQL Server 2008 R2 (10.50.x) с пакетом обновления 1 (SP1) можно также указать параметры индекса. В таком случае оптимизатор запросов будет использовать при выполнении операций поиска в индексе по указанному индексу как минимум все указанные столбцы индекса.
index_value
Имя индекса или значение идентификатора индекса. Не удается указать идентификатор индекса 0 (куча). Чтобы вернуть имя индекса или идентификатор, выполните запрос к представлению
sys.indexes
каталога.index_column_name
Имя столбца индекса, включаемого в операцию поиска. Указание
FORCESEEK
параметров индекса аналогично использованиюFORCESEEK
с указаниемINDEX
. Но более эффективного контроля над путем доступа, который использует оптимизатор запросов, можно добиться указанием и индекса, в котором следует провести поиск, и столбцов индекса, которые предполагается использовать в операции поиска. При необходимости оптимизатор может задействовать дополнительные столбцы. Например, если указан некластеризованный индекс, то оптимизатор может в дополнение к указанным столбцам выбрать использование ключевых столбцов кластеризованного индекса.
Указание FORCESEEK
можно указать следующими способами.
Синтаксис | Пример | Description |
---|---|---|
Без индекса или INDEX указания |
FROM dbo.MyTable WITH (FORCESEEK) |
Оптимизатор запросов использует только операции поиска в индексе для доступа к таблицам или представлениям через любой подходящий индекс. |
В сочетании с указанием INDEX |
FROM dbo.MyTable WITH (FORCESEEK, INDEX (MyIndex)) |
Оптимизатор запросов будет использовать при доступе к таблице или представлению через указанный индекс только операции поиска по индексу. |
Параметризация посредством указания индекса и столбцов индекса | FROM dbo.MyTable WITH (FORCESEEK (MyIndex (col1, col2, col3))) |
Оптимизатор запросов будет использовать при выполнении поиска по указанной таблице или представлению индексу как минимум указанные столбцы индекса. |
При использовании указания (с параметрами индекса или без нее FORCESEEK
) рассмотрите следующие рекомендации:
- Указание может задаваться как табличное указание или как указание запроса. Дополнительные сведения об указаниях запросов см. в статье Указания (Transact-SQL) — запросы.
- Чтобы применить
FORCESEEK
индексированное представление,NOEXPAND
необходимо также указать указание. - Указание может применяться не более одного раза для каждой таблицы или представления.
- Указание невозможно указать для удаленного источника данных. Ошибка 7377 возвращается при
FORCESEEK
указании индекса и возвращается ошибка 8180 приFORCESEEK
использовании без указания индекса. - Если
FORCESEEK
не удается найти план, возвращается ошибка 8622.
При FORCESEEK
указании параметров индекса применяются следующие рекомендации и ограничения:
- Указание нельзя указать для таблицы, которая является целью инструкции INSERT, UPDATE или DELETE.
- Указание не может быть указано в сочетании с указанием
INDEX
или другимFORCESEEK
указанием. - Должен быть указан хотя бы один столбец, который должен быть ведущим ключевым столбцом.
- Однако можно указать дополнительные столбцы индекса, но нельзя пропускать ключевые столбцы. Например, если указанный индекс содержит ключевые столбцы
a
,b
иc
, то правильный синтаксис должен содержатьFORCESEEK (MyIndex (a))
иFORCESEEK (MyIndex (a, b)
. Неправильный синтаксис может содержатьFORCESEEK (MyIndex (c))
иFORCESEEK (MyIndex (a, c)
. - Порядок следования имен столбцов, заданный в указании, должен соответствовать порядку столбцов в упоминаемом индексе.
- Столбцы, которые не указаны в определении ключа индекса, не могут быть указаны. Например, в некластеризованном индексе могут быть указаны только определенные в индексе ключевые столбцы. Кластеризованные ключевые столбцы, автоматически включенные в индекс, не могут быть указаны, но могут использоваться оптимизатором.
- Индекс columnstore, оптимизированный для памяти xVelocity, нельзя указать в качестве параметра индекса. Возвращается ошибка 366.
- Изменение определения индекса (например, добавление или удаление столбцов) может потребовать внесения изменений в запросы, ссылающиеся на этот индекс.
- Указание запрещает оптимизатору использовать пространственные индексы и XML-индексы таблицы.
- Указание не может быть указано в сочетании с указанием
FORCESCAN
. - Для секционированных индексов столбец секционирования, неявно добавленный SQL Server, не может быть указан в указании
FORCESEEK
.
Внимание
Указание FORCESEEK
параметров ограничивает количество планов, которые могут рассматриваться оптимизатором больше, чем при указании FORCESEEK
без параметров. Это может привести к тому, что ошибка Plan cannot be generated
возникает в нескольких случаях. В будущих выпусках внутренние изменения оптимизатора запросов могут привести к увеличению числа этих планов.
FORCESCAN
Область применения: SQL Server 2008 R2 (10.50.x) с пакетом обновления 1 и более поздних версий
Указывает, что оптимизатор запросов использует только операцию сканирования индекса в качестве пути доступа к указанной таблице или представлению. Указание FORCESCAN
может быть полезно для запросов, в которых оптимизатор недооценивает количество затронутых строк и выбирает операцию поиска, а не операцию сканирования. Когда это происходит, объем памяти, предоставленной для операции, слишком мал, и производительность запросов влияет.
FORCESCAN
можно указать с указанием или без нее INDEX
. При сочетании с указанием индекса (INDEX = index_name, FORCESCAN
) оптимизатор запросов рассматривает только пути сканирования доступа через указанный индекс при доступе к указанной таблице. FORCESCAN
Можно указать с указанием INDEX(0)
индекса для принудительной операции сканирования таблиц в базовой таблице.
Для секционированных таблиц и индексов FORCESCAN
применяется после устранения секций с помощью оценки предиката запроса. Это означает, что сканирование выполняется только в оставшихся секциях, а не во всей таблице.
Указание FORCESCAN
имеет следующие ограничения:
- Указание нельзя указать для таблицы, которая является целью инструкции INSERT, UPDATE или DELETE.
- Указание не может использоваться с несколькими указаниями индекса.
- Указание запрещает оптимизатору запросов рассматривать любые пространственные или XML-индексы в таблице.
- Указание невозможно указать для удаленного источника данных.
- Указание не может быть указано в сочетании с указанием
FORCESEEK
.
HOLDLOCK
Эквивалент SERIALIZABLE
. Дополнительные сведения см SERIALIZABLE
. далее в этой статье. HOLDLOCK
применяется только к таблице или представлению, для которой она указана, и только в течение длительности транзакции, определенной инструкцией, в которой она используется. HOLDLOCK
Нельзя использовать в инструкции SELECT, которая включает FOR BROWSE
этот параметр.
IGNORE_CONSTRAINTS
Применимо только в инструкции INSERT, если параметр BULK используется с OPENROWSET.
Указывает, что при операции массового импорта будут пропускаться какие-либо ограничения на таблицу. По умолчанию INSERT проверяет ограничения уникальности и проверочные ограничения и ограничения первичных и внешних ключей. Если для операции массового импорта задан параметр IGNORE_CONSTRAINTS, инструкция INSERT будет пропускать ограничения в целевой таблице. Нельзя отключить ограничения UNIQUE, PRIMARY KEY или NOT NULL.
Отключение ограничений CHECK и FOREIGN KEY может потребоваться, если введенные данные содержат нарушающие ограничения строки. При отключении ограничений CHECK и FOREIGN KEY можно импортировать данные, а затем произвести очистку данных с помощью инструкций Transact-SQL.
Однако при пропуске ограничений CHECK и FOREIGN KEY после операции каждое пропущенное ограничение помечается как is_not_trusted в представлении каталога sys.check_constraints или sys.foreign_keys. Рано или поздно придется проверить всю таблицу на соответствие ограничениям. Если таблица не была пуста перед операцией массового импорта, стоимость повторного выполнения ограничения может превысить затраты на применение ограничений CHECK и FOREIGN KEY к добавочным данным.
IGNORE_TRIGGERS
Применимо только в инструкции INSERT, если параметр BULK используется с OPENROWSET.
Указывает, что при операции объемного импорта не будут учитываться какие-либо триггеры, определенные для таблицы. По умолчанию для инструкции INSERT применяются триггеры.
Используйте IGNORE_TRIGGERS только в том случае, если приложение не зависит от триггеров и максимизации производительности важно.
NOLOCK
Эквивалент READUNCOMMITTED
. Дополнительные сведения см READUNCOMMITTED
. далее в этой статье.
Примечание.
Для инструкций UPDATE или DELETE: эта функция будет удалена в будущей версии SQL Server. Избегайте использования этого компонента в новых разработках и запланируйте изменение существующих приложений, в которых он применяется.
NOWAIT
Указывает, что ядро СУБД должно вернуть сообщение сразу после обнаружения блокировки таблицы. NOWAIT
эквивалентен указанию SET LOCK_TIMEOUT 0
для определенной таблицы. Указание NOWAIT
не работает, когда также включена подсказка TABLOCK
. Чтобы завершить запрос, не ожидая при использовании TABLOCK
указания, предусловите запрос SET LOCK_TIMEOUT 0;
вместо этого.
PAGLOCK
Применяет блокировку страниц вместо стандартной блокировки строк или ключей, а также вместо блокировки отдельной таблицы. По умолчанию используется режим блокировки, соответствующий операции. При указании в транзакциях, работающих на SNAPSHOT
уровне изоляции, блокировки страниц не принимаются, если PAGLOCK
только не объединяются с другими указаниями таблицы, для которых требуются блокировки, такие как UPDLOCK
и HOLDLOCK
.
READCOMMITTED
Указывает, что операции чтения соответствуют правилам для уровня изоляции READ COMMITTED путем использования блокировки или управления версиями строк. Если параметр READ_COMMITTED_SNAPSHOT
базы данных отключен, ядро СУБД получает общие блокировки при чтении и освобождает эти блокировки при завершении операции чтения. Если параметр READ_COMMITTED_SNAPSHOT
базы данных включен, ядро СУБД не получает блокировки и использует управление версиями строк. Дополнительные сведения об уровнях изоляции см. в статье SET TRANSACTION ISOLATION LEVEL (Transact-SQL).
Примечание.
Для инструкций UPDATE или DELETE: эта функция будет удалена в будущей версии SQL Server. Избегайте использования этого компонента в новых разработках и запланируйте изменение существующих приложений, в которых он применяется.
READCOMMITTEDLOCK
Указывает, что операции чтения соответствуют правилам для уровня изоляции READ COMMITTED путем использования блокировки. Ядро СУБД получает общие блокировки при чтении данных и освобождает эти блокировки при завершении операции чтения независимо от параметра READ_COMMITTED_SNAPSHOT
базы данных. Дополнительные сведения об уровнях изоляции см. в статье SET TRANSACTION ISOLATION LEVEL (Transact-SQL). Это указание нельзя указать в целевой таблице инструкции INSERT; Возвращается ошибка 4140.
READPAST
Указывает, что ядро СУБД не должно считывать строки и страницы, заблокированные другими транзакциями. При READPAST
указании блокировки на уровне строк пропускаются, но блокировки на уровне страницы не пропускаются. Ядро СУБД будет пропускать строки вместо блокировки текущей транзакции до тех пор, пока блокировки не будут сняты. Например, предположим, что в таблице T1
есть один целочисленный столбец со значениями 1, 2, 3, 4, 5. Если транзакция A изменяет значение 3 на 8, но еще не зафиксировано, SELECT * FROM T1 (READPAST)
возвращает значения 1, 2, 4, 5. READPAST
в основном используется для уменьшения конфликтов блокировки при реализации рабочей очереди, используюющей таблицу SQL Server. Средство чтения очередей, использующее READPAST
пропускать записи очереди, заблокированные другими транзакциями, в следующую доступную запись очереди, не ожидая, пока другие транзакции не будут освобождены их блокировки.
READPAST
Можно указать любую таблицу, указанную в инструкции UPDATE или DELETE, и любую таблицу, указанную в предложении FROM. При указании в инструкции READPAST
UPDATE применяется только при чтении данных, чтобы определить, какие записи следует обновлять, независимо от того, где он указан в инструкции. READPAST
нельзя указать для таблиц в предложении INTO инструкции INSERT. Операции обновления или удаления, которые используются READPAST
, могут блокироваться при чтении внешних ключей или индексированных представлений или при изменении вторичных индексов.
READPAST
можно указать только в транзакциях, работающих на уровнях изоляции READ COMMITTED или REPEATABLE READ. При указании в транзакциях, работающих на SNAPSHOT
уровне изоляции, READPAST
необходимо объединить с другими указаниями таблицы, для которых требуются блокировки, например UPDLOCK
и HOLDLOCK
.
Указание READPAST
таблицы нельзя указать, если READ_COMMITTED_SNAPSHOT
параметр базы данных имеет значение ON, и одно из следующих условий имеет значение true:
- Уровень изоляции транзакций сеанса имеет значение READ COMMITTED.
- Указание
READCOMMITTED
таблицы также указывается в запросе.
Чтобы указать READPAST
указание в этих случаях, удалите READCOMMITTED
указание таблицы, если оно присутствует, и добавьте READCOMMITTEDLOCK
в запрос указание таблицы.
READUNCOMMITTED
Указывает, что чтение недействительных результатов разрешено. Общие блокировки не выдаются, чтобы другие транзакции не изменяли данные, считываемые текущей транзакцией, и монопольные блокировки, заданные другими транзакциями, не блокируют чтение заблокированных данных текущей транзакции. Разрешение чтения измененных результатов может привести к повышению параллелизма за счет считывания изменений данных, откат которых произведен другими транзакциями. Это в свою очередь может сопровождаться ошибками транзакции, представлением пользователю незафиксированных данных, повторным появлением некоторых записей или их отсутствием.
READUNCOMMITTED
и NOLOCK
подсказки применяются только к блокировкам данных. Все запросы, в том числе с READUNCOMMITTED
NOLOCK
указанием, получают блокировки Sch-S (стабильность схемы) во время компиляции и выполнения. Поэтому запросы блокируются, если параллельная транзакция удерживает в таблице блокировку Sch-M (изменение схемы). Например, операция языка DDL получает блокировку Sch-M до того, как она изменяет данные схемы. При попытке получить блокировку Sch-S блокируются любые одновременные запросы, включая запущенные с READUNCOMMITTED
помощью или NOLOCK
подсказки. И наоборот, запрос, удерживающий блокировку Sch-S, блокирует параллельную транзакцию, которая пытается получить блокировку Sch-M.
READUNCOMMITTED
и NOLOCK
не может быть указан для таблиц, измененных операциями вставки, обновления или удаления. Оптимизатор запросов SQL Server игнорирует READUNCOMMITTED
и NOLOCK
указывает в предложении FROM, которое применяется к целевой таблице инструкции UPDATE или DELETE.
Примечание.
Поддержка использования READUNCOMMITTED
и NOLOCK
указания в предложении FROM, применяемого к целевой таблице инструкции UPDATE или DELETE, будет удалена в будущей версии SQL Server. Следует избегать использования этих указаний в таком контексте в новой разработке и запланировать изменение приложений, использующих их в настоящий момент.
Минимизировать состязание блокировок во время защиты транзакций от «грязных» чтений незафиксированных изменений данных можно следующими способами.
- Уровень изоляции READ COMMITTED с параметром
READ_COMMITTED_SNAPSHOT
базы данных on. - Уровень
SNAPSHOT
изоляции.
Дополнительные сведения об уровнях изоляции см. в статье SET TRANSACTION ISOLATION LEVEL (Transact-SQL).
Примечание.
Если при указании появится сообщение об ошибке 601READUNCOMMITTED
, устраните его так, как ошибка взаимоблокировки (сообщение об ошибке 1205) и повторите инструкцию.
REPEATABLEREAD
Указывает, что сканирование выполняется с той же семантикой блокировки, что и транзакция, запущенная на уровне изоляции REPEATABLE READ. Дополнительные сведения об уровнях изоляции см. в статье SET TRANSACTION ISOLATION LEVEL (Transact-SQL).
ROWLOCK
Указывает, что вместо блокировки страниц или таблиц применяются блокировки строк. При указании в транзакциях, работающих на SNAPSHOT
уровне изоляции, блокировки строк не принимаются, если ROWLOCK
они не объединяются с другими указаниями таблицы, требующими блокировки, например UPDLOCK
и HOLDLOCK
. ROWLOCK
нельзя использовать с таблицей с кластеризованным индексом columnstore. В следующем примере в приложении возвращается ошибка 651.
UPDATE [dbo].[FactResellerSalesXL_CCI] WITH (ROWLOCK)
SET UnitPrice = 50
WHERE ProductKey = 150;
SERIALIZABLE
Эквивалент HOLDLOCK
. Накладывает дополнительные ограничения на совмещаемую блокировку: удерживает ее до завершения транзакции вместо снятия блокировки сразу после того, как таблица или страница данных больше не требуется, независимо от того, завершена ли транзакция. Сканирование выполняется с той же семантикой, что и транзакция, выполняемая на SERIALIZABLE
уровне изоляции. Дополнительные сведения об уровнях изоляции см. в статье SET TRANSACTION ISOLATION LEVEL (Transact-SQL).
МОМЕНТАЛЬНЫЙ СНИМОК
Область применения: SQL Server 2014 (12.x) и более поздних версий
Доступ к оптимизированной для памяти таблице осуществляется в SNAPSHOT
изоляции. SNAPSHOT
можно использовать только с оптимизированными для памяти таблицами (не с таблицами на основе дисков), как показано в следующем примере. Дополнительные сведения см. в разделе Введение в таблицы, оптимизированные для памяти.
SELECT *
FROM dbo.Customers AS c WITH (SNAPSHOT)
LEFT JOIN dbo.[Order History] AS oh
ON c.customer_id=oh.customer_id;
SPATIAL_WINDOW_MAX_CELLS = <integer_value>
Область применения: SQL Server 2012 (11.x) и более поздних версий
Указывает максимальное количество ячеек, используемых для тесселяции геометрического или географического объекта. <integer_value> может содержать любое число в диапазоне от 1 до 8192.
Этот параметр позволяет выполнять тонкую настройку времени выполнения запроса за счет настройки компромисса между временем выполнения первичного и вторичного фильтра. Чем больше число, тем меньше время выполнения вторичного фильтра и больше время выполнения первичного фильтра, и наоборот. Для получения более плотных пространственных данных большее число должно давать большее время выполнения за счет лучшего приближения с первичным фильтром и сокращения времени выполнения вторичного фильтра. Для более разреженных данных меньшее число уменьшает время выполнения первичного фильтра.
Этот параметр работает и в ручной и в автоматической тесселяции сетки.
TABLOCK
Указывает, что полученная блокировка применяется на уровне таблицы. Тип полученной блокировки зависит от того, какая инструкция выполняется. Например, инструкция SELECT может потребовать совмещаемой блокировки. TABLOCK
Указав, общая блокировка применяется ко всей таблице, а не на уровне строки или страницы. Если HOLDLOCK
также задано, блокировка таблицы будет храниться до конца транзакции.
При импорте данных в кучу с помощью INSERT INTO <target_table> SELECT <columns> FROM <source_table>
инструкции можно включить минимальное ведение журнала и оптимальную блокировку для инструкции, указав TABLOCK
указание целевой таблицы. Кроме того, для базы данных должна быть задана простая модель восстановления или модель восстановления с неполным протоколированием. Указание TABLOCK
также позволяет параллельным вставкам в кучу или кластеризованные индексы columnstore. Дополнительные сведения см. в статье Инструкция INSERT (Transact-SQL).
При использовании с поставщиком массового набора строк OPENROWSET для импорта данных в таблицу TABLOCK
позволяет нескольким клиентам одновременно загружать данные в целевую таблицу с оптимизированным ведением журнала и блокировкой. Дополнительные сведения см. в разделе Предварительные условия для минимального протоколирования массового импорта данных.
TABLOCKX
Указывает, что к таблице применяется монопольная блокировка.
UPDLOCK
Указывает, что блокировки обновления применяются и удерживаются до завершения транзакции. UPDLOCK
принимает блокировки обновления для операций чтения только на уровне строки или на уровне страницы. Если UPDLOCK
используется TABLOCK
сочетание или блокировка уровня таблицы принимается по какой-либо другой причине, вместо этого берется монопольная блокировка (X).
При UPDLOCK
указании READCOMMITTED
READCOMMITTEDLOCK
подсказки уровня изоляции игнорируются. Например, если задан SERIALIZABLE
уровень изоляции сеанса и указан запрос (UPDLOCK
, READCOMMITTED
), READCOMMITTED
указание игнорируется, а транзакция выполняется с помощью SERIALIZABLE
уровня изоляции.
XLOCK
Указывает, что монопольные блокировки применяются и удерживаются до завершения транзакции. Если задано с ROWLOCK
, PAGLOCK
или TABLOCK
монопольные блокировки применяются к соответствующему уровню детализации.
Замечания
Подсказки таблицы игнорируются, если таблица не обращается к плану запроса. Это может быть вызвано тем, что оптимизатор вообще отказался от доступа к таблице или вместо этого получает доступ к индексированному представлению. В последнем случае доступ к индексированному представлению можно предотвратить с помощью подсказки в запросе OPTION (EXPAND VIEWS).
Все подсказки блокировки распространяются на все таблицы и представления, к которым имеет доступ данный план запроса, в том числе в таблицы и представления, на которые ссылается данное представление. Кроме того, SQL Server выполняет соответствующие проверки согласованности блокировок.
Подсказки ROWLOCK
блокировки , UPDLOCK
И XLOCK
которые получают блокировки на уровне строк, могут размещать блокировки на ключах индекса, а не фактические строки данных. Например, если для таблицы имеется некластеризованный индекс, а инструкция SELECT обрабатывается покрывающим индексом с использованием подсказки блокировки, блокировка накладывается на ключ покрывающего индекса вместо строки данных в базовой таблице.
Если таблица содержит вычисляемые столбцы, вычисляемые выражениями или функциями, обращаюющимися к столбцам в других таблицах, подсказки таблицы не используются в этих таблицах и не распространяются. Например, NOLOCK
указание таблицы указывается в таблице в запросе. В этой таблице есть столбцы, вычисляемые с помощью сочетания выражений и функций, получающих доступ к столбцам другой таблицы. Таблицы, на которые ссылаются выражения и функции, не используют подсказку NOLOCK
таблицы при доступе.
SQL Server не допускает несколько указаний таблицы из каждой из следующих групп для каждой таблицы в предложении FROM:
- Детализированные указания:
PAGLOCK
, ,NOLOCK
READCOMMITTEDLOCK
,ROWLOCK
,TABLOCK
илиTABLOCKX
. - Указания уровня изоляции:
HOLDLOCK
,NOLOCK
,READCOMMITTED
,REPEATABLEREAD
.SERIALIZABLE
Подсказки отфильтрованного индекса
Отфильтрованный индекс можно использовать в качестве указания таблицы, но оптимизатор запросов создаст ошибку 8622, если она не охватывает все строки, которые выбирает запрос. Далее приводится пример недопустимого указания отфильтрованного индекса. В примере создается отфильтрованный индекс FIBillOfMaterialsWithComponentID
, который затем используется как подсказка индекса для инструкции SELECT. Предикат отфильтрованного индекса содержит строки данных для ComponentID со значениями 533, 324 и 753. Предикат запроса также содержит строки данных для componentIDs 533, 324 и 753, но расширяет результирующий набор, чтобы включить компоненты 855 и 924, которые не входят в фильтрованный индекс. Поэтому оптимизатор запросов не может использовать отфильтрованный индекс и создает ошибку 8622. Дополнительные сведения см. в разделе Create Filtered Indexes.
IF EXISTS (SELECT name FROM sys.indexes
WHERE name = N'FIBillOfMaterialsWithComponentID'
AND object_id = OBJECT_ID(N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithComponentID
ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED INDEX "FIBillOfMaterialsWithComponentID"
ON Production.BillOfMaterials (ComponentID, StartDate, EndDate)
WHERE ComponentID IN (533, 324, 753);
GO
SELECT StartDate, ComponentID FROM Production.BillOfMaterials
WITH(INDEX (FIBillOfMaterialsWithComponentID))
WHERE ComponentID in (533, 324, 753, 855, 924);
GO
Оптимизатор запросов не будет рассматривать указание индекса, если параметры SET не имеют необходимых значений для отфильтрованных индексов. Дополнительные сведения см. в статье CREATE INDEX (Transact-SQL).
Использование NOEXPAND
NOEXPAND
применяется только к индексированных представлениям. Индексированное представление — это представление с созданным на нем уникальным кластеризованным индексом. Если запрос содержит ссылки на столбцы, присутствующие как в индексированном представлении, так и в базовых таблицах, а оптимизатор запросов определяет, что использование индексированного представления является лучшим методом выполнения запроса, оптимизатор будет использовать индекс представления. Эта функциональная возможность называется сопоставлением индексированного представления. До SQL Server 2016 (13.x) с пакетом обновления 1 (SP1) автоматическое использование индексированного представления оптимизатором запросов поддерживается только в определенных выпусках SQL Server. Так как все выпуски поддерживают автоматическое использование индексированного представления. База данных SQL Azure и Управляемый экземпляр SQL Azure также поддерживают автоматическое использование индексированных представлений без указания NOEXPAND
указания.
Дополнительные сведения см . в руководстве по архитектуре обработки запросов.
Список функций, поддерживаемых выпусками SQL Server в Windows, см. в следующих статье:
- Выпуски и поддерживаемые функции SQL Server 2022
- Выпуски и поддерживаемые функции SQL Server 2019
- Выпуски SQL Server 2017 и поддерживаемые функции
- Выпуски и поддерживаемые функции SQL Server 2016
Однако для оптимизатора запросов следует учитывать индексированные представления для сопоставления или использовать индексированное представление, на которое ссылается NOEXPAND
указание, следующие параметры SET должны иметь значение ON.
- ANSI_NULLS
- ANSI_PADDING
- ANSI_WARNINGS
- ARITHABORT 1
- CONCAT_NULL_YIELDS_NULL
- QUOTED_IDENTIFIER
1 Параметр ARITHABORT неявным образом получает значение ON, когда для ANSI_WARNINGS устанавливается ON. Поэтому вам не нужно вручную настраивать этот параметр.
Кроме того, параметр NUMERIC_ROUNDABORT нужно установить в OFF.
Чтобы принудительно оптимизатор запросов использовал индекс для индексированного представления, укажите NOEXPAND
этот параметр. Это указание можно использовать только в случае, если представление также названо в запросе. SQL Server не предоставляет указание принудительного использования определенного индексированного представления в запросе, который не называет представление непосредственно в предложении FROM. Однако оптимизатор запросов рассматривает использование индексированных представлений, даже если они не ссылаются непосредственно в запросе. Sql Server ядро СУБД автоматически создает статистику для индексированного представления при NOEXPAND
использовании указания таблицы. Опущение этого указания может привести к предупреждениям плана выполнения о отсутствии статистики, которая не может быть разрешена путем создания статистики вручную.
Во время оптимизации запросов ядро СУБД использует статистику просмотра, созданную автоматически или вручную, когда запрос ссылается непосредственно на представление и используется подсказкаNOEXPAND
.
Использование табличного указания в качестве указания запроса
Табличные указания могут использоваться в качестве указаний запроса с помощью предложения OPTION (TABLE HINT). Табличные указания рекомендуется использовать в качестве подсказок в запросах только в контексте структуры плана. Для нерегламентированных запросов укажите эти подсказки только в виде табличных подсказок. Дополнительные сведения см. в разделе Указания запросов (Transact-SQL).
Разрешения
Для указаний KEEPIDENTITY, IGNORE_CONSTRAINTS и IGNORE_TRIGGERS требуются разрешения ALTER
для таблицы.
Примеры
А. Указание TABLOCK позволяет указать метод блокировки
В следующем примере указывается, что общая блокировка принимается в Production.Product
таблице в базе данных AdventureWorks2022 и хранится до конца инструкции UPDATE.
UPDATE Production.Product
WITH (TABLOCK)
SET ListPrice = ListPrice * 1.10
WHERE ProductNumber LIKE 'BK-%';
GO
B. Используйте указание FORCESEEK, чтобы указать операцию поиска индекса
В следующем примере используется FORCESEEK
указание без указания индекса для принудительного выполнения операции Sales.SalesOrderDetail
поиска индекса в таблице в базе данных AdventureWorks2022.
SELECT *
FROM Sales.SalesOrderHeader AS h
INNER JOIN Sales.SalesOrderDetail AS d WITH (FORCESEEK)
ON h.SalesOrderID = d.SalesOrderID
WHERE h.TotalDue > 100
AND (d.OrderQty > 5 OR d.LineTotal < 1000.00);
GO
В следующем примере используется FORCESEEK
указание с индексом, чтобы заставить оптимизатор запросов выполнять операцию поиска индекса по указанному индексу и столбцу индекса.
SELECT h.SalesOrderID, h.TotalDue, d.OrderQty
FROM Sales.SalesOrderHeader AS h
INNER JOIN Sales.SalesOrderDetail AS d
WITH (FORCESEEK (PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID (SalesOrderID)))
ON h.SalesOrderID = d.SalesOrderID
WHERE h.TotalDue > 100
AND (d.OrderQty > 5 OR d.LineTotal < 1000.00);
GO
C. Использование указания FORCESCAN для указания операции сканирования индекса
В следующем примере используется FORCESCAN
указание для принудительного выполнения оптимизатора запросов для выполнения операции Sales.SalesOrderDetail
сканирования в таблице в базе данных AdventureWorks2022.
SELECT h.SalesOrderID, h.TotalDue, d.OrderQty
FROM Sales.SalesOrderHeader AS h
INNER JOIN Sales.SalesOrderDetail AS d
WITH (FORCESCAN)
ON h.SalesOrderID = d.SalesOrderID
WHERE h.TotalDue > 100
AND (d.OrderQty > 5 OR d.LineTotal < 1000.00);