提示 (TRANSACT-SQL) 的表Hints (Transact-SQL) - Table

本主题适用于:yesSQL Server(从 2008 开始)yesAzure SQL 数据库noAzure SQL 数据仓库no并行数据仓库THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

通过指定锁定方法、一个或多个索引、查询处理操作(如表扫描或索引查找)或其他选项,表提示在数据操作语言 (DML) 语句执行期间覆盖查询优化器的默认行为。Table hints override the default behavior of the query optimizer for the duration of the data manipulation language (DML) statement by specifying a locking method, one or more indexes, a query-processing operation such as a table scan or index seek, or other options. 表提示在 DML 语句的 FROM 子句中指定,仅影响在该子句中引用的表或视图。Table hints are specified in the FROM clause of the DML statement and affect only the table or view referenced in that clause.

小心

由于 SQL ServerSQL Server 查询优化器通常会为查询选择最佳执行计划,因此我们建议仅在最后迫不得已的情况下才可由资深的开发人员和数据库管理员使用提示。Because the SQL ServerSQL Server query optimizer typically selects the best execution plan for a query, we recommend that hints be used only as a last resort by experienced developers and database administrators.

适用范围:Applies to:

DELETEDELETE

InsertINSERT

SELECTSELECT

UPDATEUPDATE

MERGEMERGE

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax


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

<table_hint> ::=   
[ NOEXPAND ] {   
    INDEX  ( index_value [ ,...n ] )   
  | INDEX =  ( index_value )      
  | FORCESEEK [( index_value ( index_column_name  [ ,... ] ) ) ]  
  | FORCESCAN  
  | FORCESEEK  
  | HOLDLOCK   
  | NOLOCK   
  | NOWAIT  
  | PAGLOCK   
  | READCOMMITTED   
  | READCOMMITTEDLOCK   
  | READPAST   
  | READUNCOMMITTED   
  | REPEATABLEREAD   
  | ROWLOCK   
  | SERIALIZABLE   
  | SNAPSHOT   
  | SPATIAL_WINDOW_MAX_CELLS = integer  
  | 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   
}   

参数Arguments

( <table_hint > ) [[ ]...n ]WITH ( <table_hint> ) [ [, ]...n ]
存在一些例外情况:只有在使用 WITH 关键字指定表提示时,才支持在 FROM 子句中使用这些提示。With some exceptions, table hints are supported in the FROM clause only when the hints are specified with the WITH keyword. 指定表提示时必须使用括号。Table hints also must be specified with parentheses.

重要

不推荐省略 WITH 关键字: 后续版本的 Microsoft SQL Server 将删除该功能。This feature will be removed in a future version of Microsoft SQL Server. 请避免在新的开发工作中使用该功能,并着手修改当前还在使用该功能的应用程序。Avoid using this feature in new development work, and plan to modify applications that currently use this feature.Omitting the WITH keyword is a deprecated feature: 后续版本的 Microsoft SQL Server 将删除该功能。This feature will be removed in a future version of Microsoft SQL Server. 请避免在新的开发工作中使用该功能,并着手修改当前还在使用该功能的应用程序。Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

使用或不使用 WITH 关键字均可使用的表提示如下:NOLOCK、READUNCOMMITTED、UPDLOCK、REPEATABLEREAD、SERIALIZABLE、READCOMMITTED、TABLOCK、TABLOCKX、PAGLOCK、ROWLOCK、NOWAIT、READPAST、XLOCK、SNAPSHOT 和 NOEXPAND。The following table hints are allowed with and without the WITH keyword: NOLOCK, READUNCOMMITTED, UPDLOCK, REPEATABLEREAD, SERIALIZABLE, READCOMMITTED, TABLOCK, TABLOCKX, PAGLOCK, ROWLOCK, NOWAIT, READPAST, XLOCK, SNAPSHOT, and NOEXPAND. 如果指定的表提示不含 WITH 关键字,则必须单独指定该提示。When these table hints are specified without the WITH keyword, the hints should be specified alone. 例如:For example:

FROM t (TABLOCK)  

如果指定的提示含其他选项,则指定的提示必须含 WITH 关键字:When the hint is specified with another option, the hint must be specified with the WITH keyword:

FROM t WITH (TABLOCK, INDEX(myindex))  

建议在表提示之间使用逗号。We recommend using commas between table hints.

重要

将空格而不是逗号的提示是弃用的功能: 后续版本的 Microsoft SQL Server 将删除该功能。This feature will be removed in a future version of Microsoft SQL Server. 请不要在新的开发工作中使用该功能,并尽快修改当前还在使用该功能的应用程序。Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible.Separating hints by spaces rather than commas is a deprecated feature: 后续版本的 Microsoft SQL Server 将删除该功能。This feature will be removed in a future version of Microsoft SQL Server. 请不要在新的开发工作中使用该功能,并尽快修改当前还在使用该功能的应用程序。Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible.

NOEXPANDNOEXPAND
指定查询优化器处理查询时,不扩展任何索引视图来访问基础表。Specifies that any indexed views are not expanded to access underlying tables when the query optimizer processes the query. 查询优化器将视图当成包含聚集索引的表处理。The query optimizer treats the view like a table with clustered index. NOEXPAND 仅适用于索引视图。NOEXPAND applies only to indexed views. 有关详细信息,请参阅“备注”。For more information, see Remarks.

索引(index_value [...n ] ) |索引 = ( index_value)INDEX (index_value [,... n ] ) | INDEX = ( index_value)
INDEX() 语法指定供查询优化器在处理该语句时使用的一个或多个索引的名称或 ID。The INDEX() syntax specifies the names or IDs of one or more indexes to be used by the query optimizer when it processes the statement. 另一供选择的 INDEX = 语法指定单个索引值。The alternative INDEX = syntax specifies a single index value. 只能为每个表指定一个索引提示。Only one index hint per table can be specified.

如果存在聚集索引,则 INDEX(0) 强制执行聚集索引扫描,INDEX(1) 强制执行聚集索引扫描或查找。If a clustered index exists, INDEX(0) forces a clustered index scan and INDEX(1) forces a clustered index scan or seek. 如果不存在聚集索引,则 INDEX(0) 强制执行表扫描,INDEX(1) 被解释为错误。If no clustered index exists, INDEX(0) forces a table scan and INDEX(1) is interpreted as an error.

如果在单个提示列表中使用了多个索引,则会忽略重复项,其余列出的索引将用于检索表中的行。If multiple indexes are used in a single hint list, the duplicates are ignored and the rest of the listed indexes are used to retrieve the rows of the table. 索引提示中的索引顺序很重要。The order of the indexes in the index hint is significant. 多索引提示还强制执行索引 AND 运算,查询优化器将对所访问的每个索引应用尽可能多的条件。A multiple index hint also enforces index ANDing, and the query optimizer applies as many conditions as possible on each index accessed. 如果提示索引的集合并未包含查询引用的所有列,则会在 SQL Server 数据库引擎SQL Server Database Engine检索所有索引列后执行提取操作以检索其余列。If the collection of hinted indexes do not include all columns referenced by the query, a fetch is performed to retrieve the remaining columns after the SQL Server 数据库引擎SQL Server Database Engine retrieves all the indexed columns.

备注

如果将引用多个索引的索引提示用于星型联接中的事实数据表,则优化器将忽略索引提示,并返回一个警告消息。When an index hint referring to multiple indexes is used on the fact table in a star join, the optimizer ignores the index hint and returns a warning message. 另外,不允许对包含指定索引提示的表执行索引 OR 操作。Also, index ORing is not allowed for a table with an index hint specified.

表提示中的最大索引数为 250 个非聚集索引。The maximum number of indexes in the table hint is 250 nonclustered indexes.

KEEPIDENTITYKEEPIDENTITY
是仅在 INSERT 语句中,在使用大容量选项的OPENROWSETIs applicable only in an INSERT statement when the BULK option is used with OPENROWSET.

指定导入数据文件中的标识值用于标识列。Specifies that identity value or values in the imported data file are to be used for the identity column. 如果不指定 KEEPIDENTITY,则将验证但不导入此列的标识值。查询优化器将根据创建表时指定的种子值和增量值自动分配唯一值。If KEEPIDENTITY is not specified, the identity values for this column are verified but not imported and the query optimizer automatically assigns unique values based on the seed and increment values specified during table creation.

重要

如果数据文件不包含表或视图中的标识列的值,并且标识列不是表中的最后一列,则必须跳过标识列。If the data file does not contain values for the identity column in the table or view, and the identity column is not the last column in the table, you must skip the identity column. 有关详细信息,请参阅使用格式化文件跳过数据字段 (SQL server).For more information, see Use a Format File to Skip a Data Field (SQL Server). 如果成功跳过了一个标识列,则查询优化器自动将标识列的唯一值分配到导入的表行中。If an identity column is skipped successfully, the query optimizer automatically assigns unique values for the identity column into the imported table rows.

有关在 INSERT ...选择 * FROM openrowset (bulk) 语句,请参阅保留标识值大容量导入数据时 (SQL server).For an example that uses this hint in an INSERT ... SELECT * FROM OPENROWSET(BULK...) statement, see Keep Identity Values When Bulk Importing Data (SQL Server).

有关检查表的标识值的信息,请参阅DBCC CHECKIDENT (Transact SQL ).For information about checking the identity value for a table, see DBCC CHECKIDENT (Transact-SQL).

KEEPDEFAULTSKEEPDEFAULTS
是仅在 INSERT 语句中,在使用大容量选项的OPENROWSETIs applicable only in an INSERT statement when the BULK option is used with OPENROWSET.

指定数据记录在某一表列缺少值时插入此列的默认值(如果有),而不是插入 NULL。Specifies insertion of a table column's default value, if any, instead of NULL when the data record lacks a value for the column.

有关在 INSERT ...选择 * FROM openrowset (bulk) 语句,请参阅保留 Null 或使用默认值在大容量导入 (SQL server).For an example that uses this hint in an INSERT ... SELECT * FROM OPENROWSET(BULK...) statement, see Keep Nulls or Use Default Values During Bulk Import (SQL Server).

FORCESEEK [ (index_value(index_column_name [ ...n ] )) ]FORCESEEK [ (index_value(index_column_name [ ,... n ] )) ]
指定查询优化器仅使用索引查找操作作为表或视图中的数据的访问途径。Specifies that the query optimizer use only an index seek operation as the access path to the data in the table or view. 从 SQL Server 2008 R2 SP1 开始,还可以指定索引参数。Starting with SQL Server 2008 R2 SP1, index parameters can also be specified. 在这种情况下,查询优化器仅考虑通过指定的索引(至少使用指定的索引列)执行索引查找操作。In that case, the query optimizer considers only index seek operations through the specified index using at least the specified index columns.

index_valueindex_value
是索引名称或索引 ID 值。Is the index name or index ID value. 不能指定索引 ID 0(堆)。The index ID 0 (heap) cannot be specified. 若要返回的索引名称或 ID,请查询sys.indexes目录视图。To return the index name or ID, query the sys.indexes catalog view.

index_column_nameindex_column_name
是要包含在查找操作中的索引列的名称。Is the name of the index column to include in the seek operation. 指定带索引参数的 FORCESEEK 类似于将 FORCESEEK 与 INDEX 提示一起使用。Specifying FORCESEEK with index parameters is similar to using FORCESEEK with an INDEX hint. 但是,您可以通过指定要查找的索引和查找操作中要考虑的索引列,更好地控制查询优化器使用的访问路径。However, you can achieve greater control over the access path used by the query optimizer by specifying both the index to seek on and the index columns to consider in the seek operation. 该优化器可以根据需要考虑其他列。The optimizer may consider additional columns if needed. 例如,如果指定非聚集索引,优化器除了使用指定的列之外,还可以选择使用聚集索引键列。For example, if a nonclustered index is specified, the optimizer may choose to use clustered index key columns in addition to the specified columns.

可以通过以下方式指定 FORCESEEK 提示。The FORCESEEK hint can be specified in the following ways.

语法Syntax 示例Example DescriptionDescription
没有索引或 INDEX 提示Without an index or INDEX hint FROM dbo.MyTable WITH (FORCESEEK) 查询优化器仅考虑执行索引查找操作以通过任意相关索引访问表或视图。The query optimizer considers only index seek operations to access the table or view through any relevant index.
与 INDEX 提示组合使用Combined with an INDEX hint FROM dbo.MyTable WITH (FORCESEEK, INDEX (MyIndex)) 查询优化器仅考虑执行索引查找操作以通过指定的索引访问表或视图。The query optimizer considers only index seek operations to access the table or view through the specified index.
通过指定索引和索引列进行参数化Parameterized by specifying an index and index columns FROM dbo.MyTable WITH (FORCESEEK (MyIndex (col1, col2, col3))) 查询优化器仅考虑执行索引查找操作,以通过指定的索引(至少使用指定的索引列)访问表或视图。The query optimizer considers only index seek operations to access the table or view through the specified index using at least the specified index columns.

使用 FORCESEEK 提示(具有或不带索引参数)时,考虑以下准则。When using the FORCESEEK hint (with or without index parameters), consider the following guidelines.

  • 该提示可以指定为表提示或查询提示。The hint can be specified as a table hint or as a query hint. 有关查询提示的详细信息,请参阅查询提示 (Transact SQL ).For more information about query hints, see Query Hints (Transact-SQL).

  • 若要将 FORCESEEK 应用到索引视图,还必须指定 NOEXPAND 提示。To apply FORCESEEK to an indexed view, the NOEXPAND hint must also be specified.

  • 对每个表或视图最多应用该提示一次。The hint can be applied at most once per table or view.

  • 不能为远程数据源指定该提示。The hint cannot be specified for a remote data source. 带索引提示指定 FORCESEEK 时,将返回错误 7377;不带索引提示使用 FORCESEEK 时,将返回错误 8180。Error 7377 is returned when FORCESEEK is specified with an index hint and error 8180 is returned when FORCESEEK is used without an index hint.

  • 如果 FORCESEEK 导致找不到计划,将返回错误 8622。If FORCESEEK causes no plan to be found, error 8622 is returned.

    使用索引参数指定 FORCESEEK 时,遵循以下准则和限制。When FORCESEEK is specified with index parameters, the following guidelines and restrictions apply.

  • 不能为作为 INSERT、UPDATE 或 DELETE 语句的目标的表指定该提示。The hint cannot be specified for a table that is the target of an INSERT, UPDATE, or DELETE statement.

  • 该提示不能与 INDEX 提示或另一个 FORCESEEK 提示一起指定。The hint cannot be specified in combination with either an INDEX hint or another FORCESEEK hint.

  • 至少必须指定一个列且该列为第一个键列。At least one column must be specified and it must be the leading key column.

  • 可以指定其他索引列,但是不能跳过键列。Additional index columns can be specified, however, key columns cannot be skipped. 例如,如果指定的索引包含键列 abc,则有效的语法应包含 FORCESEEK (MyIndex (a))FORCESEEK (MyIndex (a, b)For example, if the specified index contains the key columns a, b, and c, valid syntax would include FORCESEEK (MyIndex (a)) and FORCESEEK (MyIndex (a, b). 无效的语法应包含 FORCESEEK (MyIndex (c))FORCESEEK (MyIndex (a, c)Invalid syntax would include FORCESEEK (MyIndex (c)) and FORCESEEK (MyIndex (a, c).

  • 在提示中指定的列名顺序必须与引用的索引中列的顺序匹配。The order of column names specified in the hint must match the order of the columns in the referenced index.

  • 不能指定不在索引键定义中的列。Columns that are not in the index key definition cannot be specified. 例如,在非聚集索引中,只能指定定义的索引键列。For example, in a nonclustered index, only the defined index key columns can be specified. 不能指定自动包含在索引中的聚集键列,但是优化器可以使用这些列。Clustered key columns that are automatically included in the index cannot be specified, but may be used by the optimizer.

  • xVelocity 内存优化的列存储索引不能作为索引参数指定。An xVelocity memory optimized columnstore index cannot be specified as an index parameter. 返回错误 366。Error 366 is returned.

  • 修改索引定义(例如通过添加或删除列)可能需要修改引用该索引的查询。Modifying the index definition (for example, by adding or removing columns) may require modifications to the queries that reference that index.

  • 该提示阻止优化器考虑表的任何空间或 XML 索引。The hint prevents the optimizer from considering any spatial or XML indexes on the table.

  • 该提示不能与 FORCESCAN 提示一起指定。The hint cannot be specified in combination with the FORCESCAN hint.

  • 对于分区的索引,不能在 FORCESEEK 提示中指定 SQL ServerSQL Server 隐式添加的分区列。For partitioned indexes, the partitioning column implicitly added by SQL ServerSQL Server cannot be specified in the FORCESEEK hint.

小心

指定带参数的 FORCESEEK 限制优化器可以考虑的计划数大于指定不带参数的 FORCESEEK 时的计划数。Specifying FORCESEEK with parameters limits the number of plans that can be considered by the optimizer more than when specifying FORCESEEK without parameters. 这可能导致在更多情况下出现“无法生成计划”错误。This may cause a "Plan cannot be generated" error to occur in more cases. 在未来的版本中,对优化器进行内部修改后可允许考虑更多计划。In a future release, internal modifications to the optimizer may allow more plans to be considered.

FORCESCANFORCESCAN
此提示在 SQL Server 2008 R2 SP1 中引入,它指定查询优化器仅将索引扫描操作作为引用的表或视图的访问路径。Introduced in SQL Server 2008 R2 SP1, this hint specifies that the query optimizer use only an index scan operation as the access path to the referenced table or view. 对于优化器低估受影响的行数并选择一个查找操作而非扫描操作的查询,FORCESCAN 提示很有用。The FORCESCAN hint can be useful for queries in which the optimizer underestimates the number of affected rows and chooses a seek operation rather than a scan operation. 出现这样的情况时,授予该操作的内存量太小,查询性能将受影响。When this occurs, the amount of memory granted for the operation is too small and query performance is impacted.

指定 FORCESCAN 时有无 INDEX 提示均可。FORCESCAN can be specified with or without an INDEX hint. 与索引提示组合使用 (INDEX = index_name, FORCESCAN) 时,查询优化器在访问引用的表时仅考虑通过指定的索引扫描访问路径。When combined with an index hint, (INDEX = index_name, FORCESCAN), the query optimizer considers only scan access paths through the specified index when accessing the referenced table. 可以带索引提示 INDEX(0) 指定 FORCESCAN,以强制对基表执行表扫描操作。FORCESCAN can be specified with the index hint INDEX(0) to force a table scan operation on the base table.

对于分区的表和索引,在通过查询谓词评估消除分区后应用 FORCESCAN。For partitioned tables and indexes, FORCESCAN is applied after partitions have been eliminated through query predicate evaluation. 这意味着扫描仅适用于剩余分区而非整个表。This means that the scan is applied only to the remaining partitions and not to the entire table.

FORCESCAN 提示存在以下限制。The FORCESCAN hint has the following restrictions.

  • 不能为作为 INSERT、UPDATE 或 DELETE 语句的目标的表指定该提示。The hint cannot be specified for a table that is the target of an INSERT, UPDATE, or DELETE statement.

  • 该提示不能与一个以上的索引提示一起使用。The hint cannot be used with more than one index hint.

  • 该提示阻止优化器考虑表的任何空间或 XML 索引。The hint prevents the optimizer from considering any spatial or XML indexes on the table.

  • 不能为远程数据源指定该提示。The hint cannot be specified for a remote data source.

  • 该提示不能与 FORCESEEK 提示一起指定。The hint cannot be specified in combination with the FORCESEEK hint.

    HOLDLOCKHOLDLOCK
    等同于 SERIALIZABLE。Is equivalent to SERIALIZABLE. 有关详细信息,请参阅本主题后面的 SERIALIZABLE。For more information, see SERIALIZABLE later in this topic. HOLDLOCK 仅应用于那些为其指定了 HOLDLOCK 的表或视图,并且仅在使用了 HOLDLOCK 的语句定义的事务的持续时间内应用。HOLDLOCK applies only to the table or view for which it is specified and only for the duration of the transaction defined by the statement that it is used in. HOLDLOCK 不能被用于包含 FOR BROWSE 选项的 SELECT 语句。HOLDLOCK cannot be used in a SELECT statement that includes the FOR BROWSE option.

    IGNORE_CONSTRAINTSIGNORE_CONSTRAINTS
    是仅在 INSERT 语句中,在使用大容量选项的OPENROWSETIs applicable only in an INSERT statement when the BULK option is used with OPENROWSET.

    指定大容量导入操作将忽略对表的任何约束。Specifies that any constraints on the table are ignored by the bulk-import operation. 默认情况下,会插入检查Unique Constraints and Check ConstraintsPrimary and Foreign Key ConstraintsBy default, INSERT checks Unique Constraints and Check Constraints and Primary and Foreign Key Constraints. 当为大容量导入操作指定 IGNORE_CONSTRAINTS 时,INSERT 必须忽略对目标表的这些约束。When IGNORE_CONSTRAINTS is specified for a bulk-import operation, INSERT must ignore these constraints on a target table. 注意,您无法禁用 UNIQUE、PRIMARY KEY 或 NOT NULL 约束。Note that you cannot disable UNIQUE, PRIMARY KEY, or NOT NULL constraints.

    如果输入数据包含违反约束的行,则您可能希望禁用 CHECK 和 FOREIGN KEY 约束。You might want to disable CHECK and FOREIGN KEY constraints if the input data contains rows that violate constraints. 通过禁用 CHECK 和 FOREIGN KEY 约束,可以导入数据,然后使用 Transact-SQLTransact-SQL 语句清除该数据。By disabling the CHECK and FOREIGN KEY constraints, you can import the data and then use Transact-SQLTransact-SQL statements to clean up the data.

    但是,当 CHECK 和 FOREIGN KEY 约束将被忽略,则在表上的每个忽略的约束标记为is_not_trustedsys.check_constraintssys.foreign_keys操作后目录视图。However, when CHECK and FOREIGN KEY constraints are ignored, each ignored constraint on the table is marked as is_not_trusted in the sys.check_constraints or sys.foreign_keys catalog view after the operation. 在某一时刻,应该检查全表约束。At some point, you should check the constraints on the whole table. 如果在大容量导入操作之前表不为空,则重新验证约束的开销可能超过对增量数据应用 CHECK 和 FOREIGN KEY 约束的开销。If the table was not empty before the bulk import operation, the cost of revalidating the constraint may exceed the cost of applying CHECK and FOREIGN KEY constraints to the incremental data.

    IGNORE_TRIGGERSIGNORE_TRIGGERS
    是仅在 INSERT 语句中,在使用大容量选项的OPENROWSETIs applicable only in an INSERT statement when the BULK option is used with OPENROWSET.

    指定大容量导入操作将忽略为表定义的所有触发器。Specifies that any triggers defined on the table are ignored by the bulk-import operation. 默认情况下,INSERT 将应用触发器。By default, INSERT applies triggers.

    仅当应用程序不依赖任何触发器,并且必须最大程度地提高性能时,才使用 IGNORE_TRIGGERS。Use IGNORE_TRIGGERS only if your application does not depend on any triggers and maximizing performance is important.

    NOLOCKNOLOCK
    等同于 READUNCOMMITTED。Is equivalent to READUNCOMMITTED. 有关详细信息,请参阅本主题后面的 READUNCOMMITTED。For more information, see READUNCOMMITTED later in this topic.

备注

对于 UPDATE 或 DELETE 语句: 后续版本的 Microsoft SQL Server 将删除该功能。This feature will be removed in a future version of Microsoft SQL Server. 请避免在新的开发工作中使用该功能,并着手修改当前还在使用该功能的应用程序。Avoid using this feature in new development work, and plan to modify applications that currently use this feature.For UPDATE or DELETE statements: 后续版本的 Microsoft SQL Server 将删除该功能。This feature will be removed in a future version of Microsoft SQL Server. 请避免在新的开发工作中使用该功能,并着手修改当前还在使用该功能的应用程序。Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

NOWAITNOWAIT
指示 数据库引擎Database Engine在遇到表的锁时,立即返回一条消息。Instructs the 数据库引擎Database Engine to return a message as soon as a lock is encountered on the table. NOWAIT 等同于将特定表的 SET LOCK_TIMEOUT 值指定为 0。NOWAIT is equivalent to specifying SET LOCK_TIMEOUT 0 for a specific table. 当 TABLOCK 提示也包含在内时,NOWAIT 提示不起作用。The NOWAIT hint does not work when the TABLOCK hint is also included. 若要在使用 TABLOCK 提示时终止查询而不等待,请改为在查询前加上 SETLOCK_TIMEOUT 0;To terminate a query without waiting when using the TABLOCK hint, preface the query with SETLOCK_TIMEOUT 0; instead.

PAGLOCKPAGLOCK
在通常行或键采用单个锁的地方,或者通常采用单个表锁的地方,请采用页锁。Takes page locks either where individual locks are ordinarily taken on rows or keys, or where a single table lock is ordinarily taken. 默认情况下,请使用与操作相对应的锁模式。By default, uses the lock mode appropriate for the operation. 在从 SNAPSHOT 隔离级别操作的事务中指定时,除非将 PAGLOCK 与需要锁的其他表提示(例如,UPDLOCK 和 HOLDLOCK)组合,否则不会取得页锁。When specified in transactions operating at the SNAPSHOT isolation level, page locks are not taken unless PAGLOCK is combined with other table hints that require locks, such as UPDLOCK and HOLDLOCK.

READCOMMITTEDREADCOMMITTED
指定读操作使用锁定或行版本控制来遵循有关 READ COMMITTED 隔离级别的规则。Specifies that read operations comply with the rules for the READ COMMITTED isolation level by using either locking or row versioning. 如果数据库选项 READ_COMMITTED_SNAPSHOT OFF, 数据库引擎Database Engine获取共享的锁,因为数据读取和读取的操作完成后释放这些锁。If the database option READ_COMMITTED_SNAPSHOT is OFF, the 数据库引擎Database Engine acquires shared locks as data is read and releases those locks when the read operation is completed. 如果数据库选项 READ_COMMITTED_SNAPSHOT 为 ON,则 数据库引擎Database Engine不获取锁,并使用行版本控制。If the database option READ_COMMITTED_SNAPSHOT is ON, the 数据库引擎Database Engine does not acquire locks and uses row versioning. 有关隔离级别的详细信息,请参阅SET TRANSACTION ISOLATION LEVEL (Transact SQL ).For more information about isolation levels, see SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

备注

对于 UPDATE 或 DELETE 语句: 后续版本的 Microsoft SQL Server 将删除该功能。This feature will be removed in a future version of Microsoft SQL Server. 请避免在新的开发工作中使用该功能,并着手修改当前还在使用该功能的应用程序。Avoid using this feature in new development work, and plan to modify applications that currently use this feature.For UPDATE or DELETE statements: 后续版本的 Microsoft SQL Server 将删除该功能。This feature will be removed in a future version of Microsoft SQL Server. 请避免在新的开发工作中使用该功能,并着手修改当前还在使用该功能的应用程序。Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

READCOMMITTEDLOCKREADCOMMITTEDLOCK
指定读操作使用锁定来遵循有关 READ COMMITTED 隔离级别的规则。Specifies that read operations comply with the rules for the READ COMMITTED isolation level by using locking. 无论 READ_COMMITTED_SNAPSHOT 数据库选项的设置如何, 数据库引擎Database Engine都将在读取数据时获取共享锁,在读操作完成后释放这些锁。The 数据库引擎Database Engine acquires shared locks as data is read and releases those locks when the read operation is completed, regardless of the setting of the READ_COMMITTED_SNAPSHOT database option. 有关隔离级别的详细信息,请参阅SET TRANSACTION ISOLATION LEVEL (Transact SQL ).For more information about isolation levels, see SET TRANSACTION ISOLATION LEVEL (Transact-SQL). 不能对 INSERT 语句的目标表指定此提示;将返回错误 4140。This hint cannot be specified on the target table of an INSERT statement; error 4140 is returned.

READPASTREADPAST
指定 数据库引擎Database Engine不读取由其他事务锁定的行。Specifies that the 数据库引擎Database Engine not read rows that are locked by other transactions. 当指定 READPAST 时,将跳过行级锁,但页级锁不跳过。When READPAST is specified, row-level locks are skipped but page-level locks are not skipped. 也就是说, 数据库引擎Database Engine将跳过这些行,而不是阻塞当前事务直到锁被释放。That is, the 数据库引擎Database Engine skips past the rows instead of blocking the current transaction until the locks are released. 例如,假设表 T1 包含一个单精度整数列,其值为 1、2、3、4 和 5。For example, assume table T1 contains a single integer column with the values of 1, 2, 3, 4, 5. 如果事务 A 将值 3 更改为 8,但尚未提交,则 SELECT * FROM T1 (READPAST) 将生成值 1、2、4 和 5。If transaction A changes the value of 3 to 8 but has not yet committed, a SELECT * FROM T1 (READPAST) yields values 1, 2, 4, 5. READPAST 主要用于实现使用工作队列时减少锁定争用 SQL ServerSQL Server表。READPAST is primarily used to reduce locking contention when implementing a work queue that uses a SQL ServerSQL Server table. 使用 READPAST 的队列读取器会跳过被其他事务锁定的队列项,跳至下一个可用的队列项,而不是等待其他事务释放锁。A queue reader that uses READPAST skips past queue entries locked by other transactions to the next available queue entry, without having to wait until the other transactions release their locks.

可为 UPDATE 或 DELETE 语句中以及 FROM 子句中引用的任何表指定 READPAST。READPAST can be specified for any table referenced in an UPDATE or DELETE statement, and any table referenced in a FROM clause. 如果 READPAST 是在 UPDATE 语句中指定的,则仅当读取数据以标识要更新的记录时才应用 READPAST,而不考虑语句中指定 READPAST 的位置。When specified in an UPDATE statement, READPAST is applied only when reading data to identify which records to update, regardless of where in the statement it is specified. 不能为 INSERT 语句的 INTO 子句中的表指定 READPAST。READPAST cannot be specified for tables in the INTO clause of an INSERT statement. 读取外键或索引视图或者修改辅助索引时,使用 READPAST 的更新或删除操作可能发生阻塞。Update or delete operations that use READPAST may block when reading foreign keys or indexed views, or when modifying secondary indexes.

仅可在运行于 READ COMMITTED 或 REPEATABLE READ 隔离级别的事务中指定 READPAST。READPAST can only be specified in transactions operating at the READ COMMITTED or REPEATABLE READ isolation levels. 在从 SNAPSHOT 隔离级别操作的事务中指定时,READPAST 必须与需要锁的其他表提示(例如,UPDLOCK 和 HOLDLOCK)组合。When specified in transactions operating at the SNAPSHOT isolation level, READPAST must be combined with other table hints that require locks, such as UPDLOCK and HOLDLOCK.

当 READ_COMMITTED_SNAPSHOT 数据库选项设置为 ON 并且满足以下条件之一时,无法指定 READPAST 表提示。The READPAST table hint cannot be specified when the READ_COMMITTED_SNAPSHOT database option is set to ON and either of the following conditions is true.

  • 会话的事务隔离级别为 READ COMMITTED。The transaction isolation level of the session is READ COMMITTED.

  • 查询中也指定了 READCOMMITTED 表提示。The READCOMMITTED table hint is also specified in the query.

    若要在上述情况下指定 READPAST 提示,请删除 READCOMMITTED 表提示(如果存在),然后在查询中包括 READCOMMITTEDLOCK 表提示。To specify the READPAST hint in these cases, remove the READCOMMITTED table hint if present, and include the READCOMMITTEDLOCK table hint in the query.

    READUNCOMMITTEDREADUNCOMMITTED
    指定允许脏读。Specifies that dirty reads are allowed. 不发布共享锁来阻止其他事务修改当前事务读取的数据,其他事务设置的排他锁不会阻碍当前事务读取锁定数据。No shared locks are issued to prevent other transactions from modifying data read by the current transaction, and exclusive locks set by other transactions do not block the current transaction from reading the locked data. 允许脏读可能产生较多的并发操作,但其代价是读取以后会被其他事务回滚的数据修改。Allowing dirty reads can cause higher concurrency, but at the cost of reading data modifications that then are rolled back by other transactions. 这可能会使您的事务出错,向用户显示从未提交过的数据,或者导致用户两次看到记录(或根本看不到记录)。This may generate errors for your transaction, present users with data that was never committed, or cause users to see records twice (or not at all).

    READUNCOMMITTED 和 NOLOCK 提示仅适用于数据锁。READUNCOMMITTED and NOLOCK hints apply only to data locks. 所有查询(包括那些带有 READUNCOMMITTED 和 NOLOCK 提示的查询)都会在编译和执行过程中获取 Sch-S(架构稳定性)锁。All queries, including those with READUNCOMMITTED and NOLOCK hints, acquire Sch-S (schema stability) locks during compilation and execution. 因此,当并发事务持有表的 Sch-M(架构修改)锁时,将阻塞查询。Because of this, queries are blocked when a concurrent transaction holds a Sch-M (schema modification) lock on the table. 例如,数据定义语言 (DDL) 操作在修改表的架构信息之前获取 Sch-M 锁。For example, a data definition language (DDL) operation acquires a Sch-M lock before it modifies the schema information of the table. 所有并发查询(包括那些使用 READUNCOMMITTED 或 NOLOCK 提示运行的查询)都会在尝试获取 Sch-S 锁时被阻塞。Any concurrent queries, including those running with READUNCOMMITTED or NOLOCK hints, are blocked when attempting to acquire a Sch-S lock. 相反,持有 Sch-S 锁的查询将阻塞尝试获取 Sch-M 锁的并发事务。Conversely, a query holding a Sch-S lock blocks a concurrent transaction that attempts to acquire a Sch-M lock.

    不能为通过插入、更新或删除操作修改过的表指定 READUNCOMMITTED 和 NOLOCK。READUNCOMMITTED and NOLOCK cannot be specified for tables modified by insert, update, or delete operations. SQL ServerSQL Server 查询优化器忽略 FROM 子句中应用于 UPDATE 或 DELETE 语句的目标表的 READUNCOMMITTED 和 NOLOCK 提示。The SQL ServerSQL Server query optimizer ignores the READUNCOMMITTED and NOLOCK hints in the FROM clause that apply to the target table of an UPDATE or DELETE statement.

备注

SQL ServerSQL Server 的未来版本中,将不再支持在 FROM 子句中使用应用于 UPDATE 或 DELETE 语句目标表的 READUNCOMMITTED 和 NOLOCK 提示。Support for use of the READUNCOMMITTED and NOLOCK hints in the FROM clause that apply to the target table of an UPDATE or DELETE statement will be removed in a future version of SQL ServerSQL Server. 请避免在新的开发工作上下文中使用这些提示,并计划修改当前使用它们的应用程序。Avoid using these hints in this context in new development work, and plan to modify applications that currently use them.

可以通过使用以下任意一种方法,在保护事务避免对未提交的数据修改进行脏读的同时最大程度地减少锁争用:You can minimize locking contention while protecting transactions from dirty reads of uncommitted data modifications by using either of the following:

备注

如果在指定了 READUNCOMMITTED 的情况下收到 601 号错误消息,则按解决死锁错误 (1205) 的方法解决该错误,然后重试语句。If you receive the error message 601 when READUNCOMMITTED is specified, resolve it as you would a deadlock error (1205), and retry your statement.

REPEATABLEREADREPEATABLEREAD
指定事务在 REPEATABLE READ 隔离级别运行时,使用相同的锁定语义执行一次扫描。Specifies that a scan is performed with the same locking semantics as a transaction running at REPEATABLE READ isolation level. 有关隔离级别的详细信息,请参阅SET TRANSACTION ISOLATION LEVEL (Transact SQL ).For more information about isolation levels, see SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

ROWLOCKROWLOCK
指定通常采用页锁或表锁时,采用行锁。Specifies that row locks are taken when page or table locks are ordinarily taken. 在事务在 SNAPSHOT 隔离级别操作中指定,除非 ROWLOCK 结合需要锁,如 UPDLOCK 和 HOLDLOCK 其他表提示,也不会采取行锁。When specified in transactions operating at the SNAPSHOT isolation level, row locks are not taken unless ROWLOCK is combined with other table hints that require locks, such as UPDLOCK and HOLDLOCK.

SERIALIZABLESERIALIZABLE
等同于 HOLDLOCK。Is equivalent to HOLDLOCK. 保持共享锁直到事务完成,使共享锁更具有限制性;而不是无论事务是否完成,都在不再需要所需表或数据页时立即释放共享锁。Makes shared locks more restrictive by holding them until a transaction is completed, instead of releasing the shared lock as soon as the required table or data page is no longer needed, whether the transaction has been completed or not. 执行扫描时所用的语义与在 SERIALIZABLE 隔离级别运行的事务的语义相同。The scan is performed with the same semantics as a transaction running at the SERIALIZABLE isolation level. 有关隔离级别的详细信息,请参阅SET TRANSACTION ISOLATION LEVEL (Transact SQL ).For more information about isolation levels, see SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

SNAPSHOTSNAPSHOT
适用范围SQL Server 2014SQL Server 2014SQL Server 2017SQL Server 2017Applies to: SQL Server 2014SQL Server 2014 through SQL Server 2017SQL Server 2017.

内存优化表在 SNAPSHOT 隔离下访问。The memory-optimized table is accessed under SNAPSHOT isolation. SNAPSHOT 只能用于内存优化表 (不能用于基于磁盘的表)。SNAPSHOT can only be used with memory-optimized tables (not with disk-based tables). 有关详细信息,请参阅内存优化表简介For more information, see Introduction to Memory-Optimized Tables.

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 = integerSPATIAL_WINDOW_MAX_CELLS = integer
适用范围SQL Server 2012SQL Server 2012SQL Server 2017SQL Server 2017Applies to: SQL Server 2012SQL Server 2012 through SQL Server 2017SQL Server 2017.

指定在分割 geometry 或 geography 对象时使用的最大单元格数。Specifies the maximum number of cells to use for tessellating a geometry or geography object. 是介于 1 和 8192 之间的值。number is a value between 1 and 8192.

通过使用此选项,可以在主要和辅助筛选器执行时间之间权衡性能以微调查询执行时间。This option allows for fine-tuning of query execution time by adjusting the tradeoff between primary and secondary filter execution time. 较大的数字将减少辅助筛选器执行时间,但会增加主要筛选器执行时间,而较小的数字恰相反。A larger number reduces secondary filter execution time, but increases primary execution filter time and a smaller number decreases primary filter execution time, but increase secondary filter execution. 对于较密的空间数据,较大的数字通过为主要筛选器提供更好的近似值并减少辅助筛选器执行时间,从而缩短了执行时间。For denser spatial data, a higher number should produce a faster execution time by giving a better approximation with the primary filter and reducing secondary filter execution time. 对于较稀疏的数据,较小的数字将减少主要筛选器执行时间。For sparser data, a lower number will decrease the primary filter execution time.

此选项适用于手动和自动网格分割。This option works for both manual and automatic grid tessellations.

TABLOCKTABLOCK
指定在表级别应用获取的锁。Specifies that the acquired lock is applied at the table level. 获取的锁类型取决于正在执行的语句。The type of lock that is acquired depends on the statement being executed. 例如,SELECT 语句可能获取一个共享锁。For example, a SELECT statement may acquire a shared lock. 通过指定 TABLOCK,将该共享锁应用到整个表而非在行或页级别应用。By specifying TABLOCK, the shared lock is applied to the entire table instead of at the row or page level. 如果同时指定了 HOLDLOCK,则会一直持有表锁,直至事务结束。If HOLDLOCK is also specified, the table lock is held until the end of the transaction.

使用 INSERT INTO 到堆中导入数据时<target_table > 选择<列 > FROM <source_table > 语句,可以启用优化的日志记录和通过指定锁定语句对目标表的 TABLOCK 提示。When importing data into a heap by using the INSERT INTO <target_table> SELECT <columns> FROM <source_table> statement, you can enable optimized logging and locking for the statement by specifying the TABLOCK hint for the target table. 此外,数据库的恢复模式必须设置为简单或大容量日志模式。In addition, the recovery model of the database must be set to simple or bulk-logged. 有关详细信息,请参阅 INSERT (Transact-SQL)For more information, see INSERT (Transact-SQL).

如果用于OPENROWSET大容量行集提供程序将数据导入的表,TABLOCK 允许多个客户端同时将数据加载到目标表具有优化日志记录和锁定。When used with the OPENROWSET bulk rowset provider to import data into a table, TABLOCK enables multiple clients to concurrently load data into the target table with optimized logging and locking. 有关详细信息,请参阅Prerequisites for Minimal Logging 中大容量导入For more information, see Prerequisites for Minimal Logging in Bulk Import.

TABLOCKXTABLOCKX
指定对表采用排他锁。Specifies that an exclusive lock is taken on the table.

UPDLOCKUPDLOCK
指定采用更新锁并保持到事务完成。Specifies that update locks are to be taken and held until the transaction completes. UPDLOCK 仅对行级别或页级别的读操作采用更新锁。UPDLOCK takes update locks for read operations only at the row-level or page-level. 如果将 UPDLOCK 与 TABLOCK 组合使用或出于一些其他原因采用表级锁,将采用排他 (X) 锁。If UPDLOCK is combined with TABLOCK, or a table-level lock is taken for some other reason, an exclusive (X) lock will be taken instead.

指定 UPDLOCK 时,忽略 READCOMMITTED 和 READCOMMITTEDLOCK 隔离级别提示。When UPDLOCK is specified, the READCOMMITTED and READCOMMITTEDLOCK isolation level hints are ignored. 例如,如果将会话的隔离级别设置为 SERIALIZABLE 且查询指定 (UPDLOCK, READCOMMITTED),则忽略 READCOMMITTED 提示且使用 SERIALIZABLE 隔离级别运行事务。For example, if the isolation level of the session is set to SERIALIZABLE and a query specifies (UPDLOCK, READCOMMITTED), the READCOMMITTED hint is ignored and the transaction is run using the SERIALIZABLE isolation level.

XLOCKXLOCK
指定采用排他锁并保持到事务完成。Specifies that exclusive locks are to be taken and held until the transaction completes. 如果同时指定了 ROWLOCK, PAGLOCK 或 TABLOCK,则排他锁将应用于相应的粒度级别。If specified with ROWLOCK, PAGLOCK, or TABLOCK, the exclusive locks apply to the appropriate level of granularity.

注释Remarks

如果查询计划不访问表,则将忽略表提示。The table hints are ignored if the table is not accessed by the query plan. 这可能是由于优化器选择了完全不访问该表,也可能是因为改成了访问索引视图。This may be caused by the optimizer choosing not to access the table at all, or because an indexed view is accessed instead. 在后一种情况中,使用 OPTION (EXPAND VIEWS) 查询提示可阻止访问索引视图。In the latter case, accessing an indexed view can be prevented by using the OPTION (EXPAND VIEWS) query hint.

所有锁提示将传播到查询计划访问的所有表和视图,其中包括在视图中引用的表和视图。All lock hints are propagated to all the tables and views that are accessed by the query plan, including tables and views referenced in a view. 另外, SQL ServerSQL Server 还将执行对应的锁一致性检查。Also, SQL ServerSQL Server performs the corresponding lock consistency checks.

获取行级别锁的锁提示 ROWLOCK、UPDLOCK 和 XLOCK 可能对索引键而不是实际的数据行采用锁。Lock hints ROWLOCK, UPDLOCK, AND XLOCK that acquire row-level locks may place locks on index keys rather than the actual data rows. 例如,如果表具有非聚集索引,而且由涵盖索引处理使用锁提示的 SELECT 语句,则获得的锁针对的是涵盖索引中的索引键,而不是基表中的数据行。For example, if a table has a nonclustered index, and a SELECT statement using a lock hint is handled by a covering index, a lock is acquired on the index key in the covering index rather than on the data row in the base table.

如果表包含计算列,而该计算列是由访问其他表中的列的表达式或函数计算的,则不在这些表中使用表提示,并且不会传播这些提示。If a table contains computed columns that are computed by expressions or functions accessing columns in other tables, the table hints are not used on those tables and are not propagated. 例如,在查询的表中指定 NOLOCK 表提示。For example, a NOLOCK table hint is specified on a table in the query. 此表包含的计算列是由访问另一表中的列的表达式和函数组合计算的。This table has computed columns that are computed by a combination of expressions and functions that access columns in another table. 表达式和函数引用的表在被访问时将不使用 NOLOCK 表提示。The tables referenced by the expressions and functions do not use the NOLOCK table hint when accessed.

对于 FROM 子句中的每个表, SQL ServerSQL Server 不允许存在多个来自以下各个组的表提示: SQL ServerSQL Server does not allow for more than one table hint from each of the following groups for each table in the FROM clause:

  • 粒度提示:PAGLOCK、NOLOCK、READCOMMITTEDLOCK、ROWLOCK、TABLOCK 或 TABLOCKX。Granularity hints: PAGLOCK, NOLOCK, READCOMMITTEDLOCK, ROWLOCK, TABLOCK, or TABLOCKX.

  • 隔离级别提示:HOLDLOCK、NOLOCK、READCOMMITTED、REPEATABLEREAD 和 SERIALIZABLE。Isolation level hints: HOLDLOCK, NOLOCK, READCOMMITTED, REPEATABLEREAD, SERIALIZABLE.

筛选索引提示Filtered Index Hints

筛选索引可用作表提示,但如果未涵盖查询选择的所有行,则会导致查询优化器产生错误 8622。A filtered index can be used as a table hint, but will cause the query optimizer to generate error 8622 if it does not cover all of the rows that the query selects. 下面是一个无效筛选索引提示的示例。The following is an example of an invalid filtered index hint. 该示例创建了筛选索引 FIBillOfMaterialsWithComponentID,然后将其用作 SELECT 语句的索引提示。The example creates the filtered index FIBillOfMaterialsWithComponentID and then uses it as an index hint for a SELECT statement. 筛选索引谓词包含 ComponentID 为 533、324 和 753 的数据行。The filtered index predicate includes data rows for ComponentIDs 533, 324, and 753. 查询谓词也包含 ComponentID 为 533、324 和 753 的数据行,但它扩展了结果集,使之包含 ComponentID 为 855 和 924 的数据行,而筛选索引中则不包含这两行。The query predicate also includes data rows for ComponentIDs 533, 324, and 753 but extends the result set to include ComponentIDs 855 and 924, which are not in the filtered index. 因此,查询优化器无法使用此筛选索引提示,并产生错误 8622。Therefore, the query optimizer cannot use the filtered index hint and generates error 8622. 有关详细信息,请参阅 Create Filtered IndexesFor more information, see 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 选项不包含筛选索引所需的值,查询优化器将不考虑使用索引提示。The query optimizer will not consider an index hint if the SET options do not have the required values for filtered indexes. 有关详细信息,请参阅 CREATE INDEX (Transact-SQL)For more information, see CREATE INDEX (Transact-SQL).

使用 NOEXPANDUsing NOEXPAND

NOEXPAND 仅适用于索引视图NOEXPAND applies only to indexed views. 索引视图是包含为其创建的唯一聚集索引的视图。An indexed view is a view with a unique clustered index created on it. 如果查询包含对同时存在于索引视图和基表中的列的引用,而且查询优化器确定执行查询的最佳方法是使用索引视图,则查询优化器将对视图使用索引。If a query contains references to columns that are present both in an indexed view and base tables, and the query optimizer determines that using the indexed view provides the best method for executing the query, the query optimizer uses the index on the view. 此功能称为索引视图匹配This functionality is called indexed view matching. 之前 SQL Server 2016SQL Server 2016的特定版本中仅支持 SP1,索引视图查询优化器自动使用 SQL ServerSQL ServerPrior to SQL Server 2016SQL Server 2016 SP1, automatic use of an indexed view by the query optimizer is supported only in specific editions of SQL ServerSQL Server. 有关 SQL ServerSQL Server各版本支持的功能列表,请参阅 SQL Server 2016 各个版本支持的功能For a list of features that are supported by the editions of SQL ServerSQL Server, see Features Supported by the Editions of SQL Server 2016.

但是,为了使优化器考虑使用索引视图进行匹配,或者使用通过 NOEXPAND 提示引用的索引视图,则必须将以下 SET 选项设置为 ON。However, for the optimizer to consider indexed views for matching, or use an indexed view that is referenced with the NOEXPAND hint, the following SET options must be set to ON.

备注

Azure SQL 数据库支持自动使用索引视图,而无需指定了 NOEXPAND 提示。Azure SQL Database supports automatic use of indexed views without specifying the NOEXPAND hint.

ANSI_NULLSANSI_NULLS ANSI_WARNINGSANSI_WARNINGS CONCAT_NULL_YIELDS_NULLCONCAT_NULL_YIELDS_NULL
ANSI_PADDINGANSI_PADDING ARITHABORT1ARITHABORT1 QUOTED_IDENTIFIERQUOTED_IDENTIFIER

1 ARITHABORT 隐式设置为 ON 时 ANSI_WARNINGS 设置为 ON。1 ARITHABORT is implicitly set to ON when ANSI_WARNINGS is set to ON. 因此,不必手动调整此设置。Therefore, you do not have to manually adjust this setting.

另外,必须将 NUMERIC_ROUNDABORT 选项设置为 OFF。Also, the NUMERIC_ROUNDABORT option must be set to OFF.

若要强制优化器对索引视图使用索引,请指定 NOEXPAND 选项。To force the optimizer to use an index for an indexed view, specify the NOEXPAND option. 仅当查询中也命名了此视图时才能使用此提示。This hint can be used only if the view is also named in the query. 如果某个查询没有在 FROM 子句中直接命名特定索引视图,则 SQL ServerSQL Server 不提供用于在此查询中强制使用此视图的提示;但是,即使查询中未直接引用索引视图,查询优化器仍会考虑使用索引视图。 SQL ServerSQL Server does not provide a hint to force a particular indexed view to be used in a query that does not name the view directly in the FROM clause; however, the query optimizer considers using indexed views, even if they are not referenced directly in the query.

将表提示用作查询提示Using a Table Hint as a Query Hint

表提示还可以指定通过使用 OPTION (TABLE HINT) 子句为查询提示。Table hints can also be specified as a query hint by using the OPTION (TABLE HINT) clause. 我们建议作为查询提示的上下文中仅使用表提示计划指南We recommend using a table hint as a query hint only in the context of a plan guide. 对于即席查询,请将这些提示仅指定为表提示。For ad-hoc queries, specify these hints only as table hints. 有关详细信息,请参阅查询提示 (Transact-SQL)For more information, see Query Hints (Transact-SQL).

PermissionsPermissions

KEEPIDENTITY、IGNORE_CONSTRAINTS 和 IGNORE_TRIGGERS 提示需要具有对表的 ALTER 权限。The KEEPIDENTITY, IGNORE_CONSTRAINTS, and IGNORE_TRIGGERS hints require ALTER permissions on the table.

示例Examples

A.A. 使用 TABLOCK 提示指定锁定方法Using the TABLOCK hint to specify a locking method

下面的示例指定对 AdventureWorks2012AdventureWorks2012 数据库中的 Production.Product 表采用共享锁,并保持到 UPDATE 语句结束。The following example specifies that a shared lock is taken on the Production.Product table in the AdventureWorks2012AdventureWorks2012 database and is held until the end of the UPDATE statement.

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

B.B. 使用 FORCESEEK 提示指定索引查找操作Using the FORCESEEK hint to specify an index seek operation

以下示例使用未指定索引的 FORCESEEK 提示强制查询优化器对 AdventureWorks2012AdventureWorks2012 数据库中的 Sales.SalesOrderDetail 表执行索引查找操作。The following example uses the FORCESEEK hint without specifying an index to force the query optimizer to perform an index seek operation on the Sales.SalesOrderDetail table in the AdventureWorks2012AdventureWorks2012 database.

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 提示强制查询优化器对指定的索引和索引列执行索引查找操作。The following example uses the FORCESEEK hint with an index to force the query optimizer to perform an index seek operation on the specified index and index column.

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.C. 使用 FORCESCAN 提示指定索引扫描操作Using the FORCESCAN hint to specify an index scan operation

以下示例使用 FORCESCAN 提示强制查询优化器对 AdventureWorks2012AdventureWorks2012 数据库中的 Sales.SalesOrderDetail 表执行扫描操作。The following example uses the FORCESCAN hint to force the query optimizer to perform a scan operation on the Sales.SalesOrderDetail table in the AdventureWorks2012AdventureWorks2012 database.

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);  

另请参阅See Also

OPENROWSET (Transact-SQL) OPENROWSET (Transact-SQL)
提示 (Transact SQL ) Hints (Transact-SQL)
查询提示 (Transact-SQL)Query Hints (Transact-SQL)