原文:msdn 参考: 点击打开链接
通过指定锁定方法、一个或多个索引、查询处理操作(如表扫描或索引查找)或其他选项,表提示可在数据操作语言 (DML) 语句执行期间覆盖查询优化器的默认行为。表提示在 DML 语句的 FROM 子句中指定,仅影响在该子句中引用的表或视图。
注意 |
---|
由于 SQL Server 查询优化器通常会为查询选择最佳执行计划,因此我们建议仅在最后迫不得已的情况下才可由资深的开发人员和数据库管理员使用提示。 |
适用范围:
WITH ( <table_hint> [ [, ]...n ] ) <table_hint> ::= [ NOEXPAND ] { INDEX ( index_value [ ,...n ] ) | INDEX = ( index_value ) | FASTFIRSTROW | FORCESEEK [( index_value ( index_column_name [ ,... ] ) ) ] | FORCESCAN | 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 | NOLOCK | NOWAIT | PAGLOCK | READCOMMITTED | READCOMMITTEDLOCK | READPAST | REPEATABLEREAD | ROWLOCK | SERIALIZABLE | TABLOCK | TABLOCKX | UPDLOCK | XLOCK }
如果查询计划不访问表,则将忽略表提示。这可能是由于优化器选择了完全不访问该表,也可能是因为改成了访问索引视图。在后一种情况中,使用 OPTION (EXPAND VIEWS) 查询提示可阻止访问索引视图。
所有锁提示将传播到查询计划访问的所有表和视图,其中包括在视图中引用的表和视图。另外,SQL Server 还将执行对应的锁一致性检查。
获取行级别锁的锁提示 ROWLOCK、UPDLOCK 和 XLOCK 可能对索引键而不是实际的数据行采用锁。例如,如果表具有非聚集索引,而且由涵盖索引来处理使用锁提示的 SELECT 语句,则获得的锁针对的是涵盖索引中的索引键而不是基表中的数据行。
如果某个表包含计算列,而该计算列是由访问其他某些表中的列的表达式或函数计算的,则不对这些表使用表提示,即不传播表提示。例如,对查询中的表指定 NOLOCK 表提示。此表包含的计算列是由访问另一表中的列的表达式和函数组合计算的。表达式和函数引用的表在被访问时将不使用 NOLOCK 表提示。
对于 FROM 子句中的每个表,SQL Server 不允许存在多个来自以下各个组的表提示:
-
粒度提示:PAGLOCK、NOLOCK、READCOMMITTEDLOCK、ROWLOCK、TABLOCK 或 TABLOCKX。
-
隔离级别提示:HOLDLOCK、NOLOCK、READCOMMITTED、REPEATABLEREAD、SERIALIZABLE。
筛选索引提示
筛选索引可用作表提示,但如果它未将查询选择的所有行都包含在内,将导致查询优化器产生错误 8622。下面是一个无效筛选索引提示的示例。该示例创建了筛选索引 FIBillOfMaterialsWithComponentID,然后将其用作 SELECT 语句的索引提示。筛选索引谓词包含 ComponentID 为 533、324 和 753 的数据行。查询谓词也包含 ComponentID 为 533、324 和 753 的数据行,但它扩展了结果集,使之包含 ComponentID 为 855 和 924 的数据行,而筛选索引中则不包含这两行。因此,查询优化器无法使用此筛选索引提示,并产生错误 8622。有关详细信息,请参阅筛选索引设计准则。
USE AdventureWorks2008R2; GO IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'FIBillOfMaterialsWithComponentID' AND object_id = OBJECT_ID(N'Production.BillOfMaterials')) DROP INDEX FIBillOfMaterialsWithComponentID ON Production.BillOfMaterials; GO CREATE NONCLUSTERED INDEX "FIBillOfMaterialsWithComponentID" ON Production.BillOfMaterials (ComponentID, StartDate, EndDate) WHERE ComponentID IN (533, 324, 753); GO SELECT StartDate, ComponentID FROM Production.BillOfMaterials WITH( INDEX (FIBillOfMaterialsWithComponentID) ) WHERE ComponentID in (533, 324, 753, 855, 924); GO
如果 SET 选项不包含筛选索引所需的值,查询优化器将不考虑使用索引提示。有关详细信息,请参阅 CREATE INDEX (Transact-SQL)。
使用 NOEXPAND
NOEXPAND 只适用于索引视图。索引视图是包含为其创建的唯一聚集索引的视图。如果查询包含对同时存在于索引视图和基表中的列的引用,而且查询优化器确定执行查询的最佳方法是使用索引视图,则查询优化器将对视图使用索引。此功能称为“索引视图匹配”,只有 SQL Server Enterprise Edition 和 Developer Edition 支持此功能。
但是,为了使优化器考虑使用索引视图进行匹配或在引用索引视图时使用 NOEXPAND 提示,必须将以下 SET 选项设置为 ON:
ANSI_NULLS | ANSI_WARNINGS | CONCAT_NULL_YIELDS_NULL |
ANSI_PADDING | ARITHABORT1 | QUOTED_IDENTIFIERS |
1 如果 ANSI_WARNINGS 设置为 ON,则 ARITHABORT 将隐式设置为 ON。因此,不必手动调整此设置。
另外,必须将 NUMERIC_ROUNDABORT 选项设置为 OFF。
若要强制优化器对索引视图使用索引,请指定 NOEXPAND 选项。仅当查询中也命名了此视图时才能使用此提示。如果某个查询没有在 FROM 子句中直接命名特定索引视图,则 SQL Server 不提供用于在此查询中强制使用此视图的提示;但是,即使查询中未直接引用索引视图,查询优化器仍会考虑使用索引视图。
有关详细信息,请参阅解析视图的索引。
将表提示用作查询提示
通过使用 OPTION (TABLE HINT) 子句,也可将表提示指定为查询提示。我们建议仅在计划指南的上下文中将表提示用作查询提示。对于即席查询,请将这些提示仅指定为表提示。有关详细信息,请参阅查询提示 (Transact-SQL)。
A. 使用 TABLOCK 提示指定锁定方法
下面的示例指定对 Production.Product 表采用共享锁,并保持到 UPDATE 语句结束。
USE AdventureWorks2008R2; GO UPDATE Production.Product WITH (TABLOCK) SET ListPrice = ListPrice * 1.10 WHERE ProductNumber LIKE 'BK-%'; GO
B. 使用 FORCESEEK 提示指定索引查找操作
以下示例使用未指定索引的 FORCESEEK 提示强制查询优化器对 Sales.SalesOrderDetail 表执行索引查找操作。
USE AdventureWorks2008R2; GO SELECT * FROM Sales.SalesOrderHeader AS h INNER JOIN Sales.SalesOrderDetail AS d WITH (FORCESEEK) ON h.SalesOrderID = d.SalesOrderID WHERE h.TotalDue > 100 AND (d.OrderQty > 5 OR d.LineTotal < 1000.00); GO
以下示例使用指定索引的 FORCESEEK 提示强制查询优化器对指定的索引和索引列执行索引查找操作。
USE AdventureWorks2008R2; GO 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. 使用 FORCECAN 提示指定索引扫描操作
以下示例使用 FORCESCAN 提示强制查询优化器对 Sales.SalesOrderDetail 表执行索引扫描操作。
USE AdventureWorks2008R2; GO 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);