表提示 (Transact-SQL)

通过指定锁定方法、一个或多个索引、查询处理操作（如表扫描或索引查找）或其他选项，表提示可在数据操作语言 (DML) 语句执行期间覆盖查询优化器的默认行为。

注意
由于 SQL Server 查询优化器通常会为查询选择最佳执行计划，因此我们建议仅在最后迫不得已的情况下才可由资深的开发人员和数据库管理员使用提示。

适用范围：

DELETE

INSERT

SELECT

UPDATE

MERGE

Transact-SQL 语法约定

语法

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

<table_hint> ::= 
[ NOEXPAND ] { 
    INDEX ( index_value [ ,...n ] ) | INDEX = ( index_value )
  | FASTFIRSTROW 
  | FORCESEEK
  | HOLDLOCK 
  | NOLOCK 
  | NOWAIT
  | PAGLOCK 
  | READCOMMITTED 
  | READCOMMITTEDLOCK 
  | READPAST 
  | READUNCOMMITTED 
  | REPEATABLEREAD 
  | ROWLOCK 
  | SERIALIZABLE 
  | TABLOCK 
  | TABLOCKX 
  | UPDLOCK 
  | XLOCK 
} 

<table_hint_limited> ::=
{
    KEEPIDENTITY 
  | KEEPDEFAULTS 
  | FASTFIRSTROW 

  | HOLDLOCK 
  | IGNORE_CONSTRAINTS 
  | IGNORE_TRIGGERS 
  | NOWAIT
  | PAGLOCK 
  | READCOMMITTED 
  | READCOMMITTEDLOCK 
  | READPAST 
  | REPEATABLEREAD 
  | ROWLOCK 
  | SERIALIZABLE 
  | TABLOCK 
  | TABLOCKX 
  | UPDLOCK 
  | XLOCK 
} 

参数

• WITH ( <table_hint> ) [ [ , ]...n ]
  也存在一些例外情况：仅当用 WITH 关键字指定表提示时，才支持在 FROM 子句中使用这些提示。指定表提示时必须使用括号。

  重要提示
  不推荐省略 WITH 关键字：后续版本的 Microsoft SQL Server 将删除该功能。 请避免在新的开发工作中使用该功能，并着手修改当前还在使用该功能的应用程序。

  使用或不使用 WITH 关键字均可使用的表提示如下：NOLOCK、READUNCOMMITTED、UPDLOCK、REPEATABLEREAD、SERIALIZABLE、READCOMMITTED、FASTFIRSTROW、TABLOCK、TABLOCKX、PAGLOCK、ROWLOCK、NOWAIT、READPAST、XLOCK 和 NOEXPAND。如果指定的表提示不含 WITH 关键字，则必须单独指定该提示。例如：

  FROM t (TABLOCK)
  

  如果指定的提示含其他选项，则指定的提示必须含 WITH 关键字：

  FROM t WITH (TABLOCK, INDEX(myindex))
  

  建议在表提示之间使用逗号。

  重要提示
  不推荐用空格而不是逗号分隔提示：后续版本的 Microsoft SQL Server 将删除该功能。 请不要在新的开发工作中使用该功能，并尽快修改当前还在使用该功能的应用程序。

  在针对兼容级别为 90 及更高的数据库的查询中使用提示时，即存在这些限制。

• NOEXPAND
  指定查询优化器处理查询时，不扩展任何索引视图来访问基础表。查询优化器将视图当成包含聚集索引的表处理。NOEXPAND 只适用于索引视图。有关详细信息，请参阅“备注”。

• INDEX ( index_value [ ,... n] ) | INDEX = ( index_value )
  INDEX() 语法指定供查询优化器在处理该语句时使用的一个或多个索引的名称或 ID。另一供选择的 INDEX = 语法指定单个索引值。只能为每个表指定一个索引提示。

  如果存在聚集索引，则 INDEX(0) 强制执行聚集索引扫描，INDEX(1) 强制执行聚集索引扫描或查找。如果不存在聚集索引，则 INDEX(0) 强制执行表扫描，INDEX(1) 被解释为错误。

  如果在单个提示列表中使用了多个索引，则会忽略重复项，其余列出的索引将用于检索表中的行。索引提示中的索引顺序很重要。多索引提示还强制执行索引 AND 运算，查询优化器将对所访问的每个索引应用尽可能多的条件。如果提示索引的集合未将查询引用的所有列都包含在内，则在 SQL Server 数据库引擎检索所有索引列后，将执行提取操作以检索剩余的列。

  注意
  如果将引用多个索引的索引提示用于星型联接中的事实数据表，则优化器将忽略索引提示，并返回一个警告消息。另外，不允许对包含指定索引提示的表执行索引 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...)语句中使用此提示的示例，请参阅在大容量导入期间保留空值或使用默认值。

• FASTFIRSTROW
  等同于 OPTION (FAST 1)。有关详细信息，请参阅查询提示 (Transact-SQL)。

  重要提示
  下一版本的 Microsoft SQL Server 将删除该功能。 请不要在新的开发工作中使用该功能，并尽快修改当前还在使用该功能的应用程序。

• FORCESEEK
  指定查询优化器仅使用索引查找操作作为访问查询中所引用表或视图中的数据的途经。

  FORCESEEK 同时适用于聚集索引查找和非聚集索引查找操作。可以在 SELECT 语句的 FROM 子句和 UPDATE、MERGE 或 DELETE 语句的 FROM <table_source> 子句中为任何表或视图指定 FORCESEEK。

  指定 FORCESEEK 时有无 INDEX 提示均可。与索引提示结合使用时，查询优化器仅考虑通过指定索引的查找访问途经。如果 FORCESEEK 导致找不到计划，将返回错误 8622。有关详细信息，请参阅使用 FORCESEEK 表提示。

• HOLDLOCK
  等同于 SERIALIZABLE。有关详细信息，请参阅本主题后面的 SERIALIZABLE。HOLDLOCK 仅应用于那些为其指定了 HOLDLOCK 的表或视图，并且仅在使用了 HOLDLOCK 的语句定义的事务的持续时间内应用。HOLDLOCK 不能被用于包含 FOR BROWSE 选项的 SELECT 语句。

• 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 约束时，操作之后表的每个忽略的约束都将在 sys.check_constraints 或 sys.foreign_keys 目录视图中标记为 is_not_trusted。在某一时刻，应该检查全表约束。如果在大容量导入操作之前表不为空，则重新验证约束的开销可能超过对增量数据应用 CHECK 和 FOREIGN KEY 约束的开销。

• IGNORE_TRIGGERS
  只适用于 INSERT 语句（当 BULK 选项与 OPENROWSET 一起使用时）。

  指定大容量导入操作将忽略为表定义的所有触发器。默认情况下，INSERT 将应用触发器。

  仅当应用程序不依赖任何触发器，并且必须最大程度地提高性能时，才使用 IGNORE_TRIGGERS。

• NOLOCK
  等同于 READUNCOMMITTED。有关详细信息，请参阅本主题后面的 READUNCOMMITTED。

  注意
  对于 UPDATE 或 DELETE 语句：后续版本的 Microsoft SQL Server 将删除该功能。 请避免在新的开发工作中使用该功能，并着手修改当前还在使用该功能的应用程序。

• NOWAIT
  指示数据库引擎在遇到表的锁时，立即返回一条消息。NOWAIT 等同于将特定表的 SET LOCK_TIMEOUT 值指定为 0。

• PAGLOCK
  在通常行或键采用单个锁的地方，或者通常采用单个表锁的地方，请采用页锁。默认情况下，请使用与操作相对应的锁模式。在从 SNAPSHOT 隔离级别操作的事务中指定时，除非将 PAGLOCK 与需要锁的其他表提示（例如，UPDLOCK 和 HOLDLOCK）组合，否则不会取得页锁。

• READCOMMITTED
  指定读操作使用锁定或行版本控制来遵循有关 READ COMMITTED 隔离级别的规则。如果数据库选项 READ_COMMITTED_SNAPSHOT 为 OFF，数据库引擎将在读取数据时获取共享锁，在读操作完成后释放这些锁。如果数据库选项 READ_COMMITTED_SNAPSHOT 为 ON，则数据库引擎不获取锁，并使用行版本控制。有关隔离级别的详细信息，请参阅 SET TRANSACTION ISOLATION LEVEL (Transact-SQL)。

  注意
  对于 UPDATE 或 DELETE 语句：后续版本的 Microsoft SQL Server 将删除该功能。 请避免在新的开发工作中使用该功能，并着手修改当前还在使用该功能的应用程序。

• READCOMMITTEDLOCK
  指定读操作使用锁定来遵循有关 READ COMMITTED 隔离级别的规则。无论 READ_COMMITTED_SNAPSHOT 数据库选项的设置如何，数据库引擎都将在读取数据时获取共享锁，在读操作完成后释放这些锁。有关隔离级别的详细信息，请参阅 SET TRANSACTION ISOLATION LEVEL (Transact-SQL)。

• READPAST
  指定数据库引擎不读取由其他事务锁定的行。在大多数情况下，这同样适用于页。如果指定了 READPAST，则行级锁和页级锁都将被跳过。也就是说，数据库引擎将跳过这些行或页，而不是阻塞当前事务直到锁被释放。例如，假设表 T1 包含一个单精度整数列，其值为 1、2、3、4 和 5。如果事务 A 将值 3 更改为 8，但尚未提交，则 SELECT * FROM T1 (READPAST) 将生成值 1、2、4 和 5。使用 SQL Server 表实现工作队列时，READPAST 主要用于减少锁定争用。使用 READPAST 的队列读取器会跳过被其他事务锁定的队列项，跳至下一个可用的队列项，而不是等待其他事务释放锁。

  可为 UPDATE 或 DELETE 语句中以及 FROM 子句中引用的任何表指定 READPAST。如果 READPAST 是在 UPDATE 语句中指定的，则仅当读取数据以标识要更新的记录时才应用 READPAST，而不考虑语句中指定 READPAST 的位置。不能为 INSERT 语句的 INTO 子句中的表指定 READPAST。使用 READPAST 的读操作不会发生阻塞。读取外键或索引视图或者修改辅助索引时，使用 READPAST 的更新或删除操作可能发生阻塞。

  仅可在运行于 READ COMMITTED 或 REPEATABLE READ 隔离级别的事务中指定 READPAST。在从 SNAPSHOT 隔离级别操作的事务中指定时，READPAST 必须与需要锁的其他表提示（例如，UPDLOCK 和 HOLDLOCK）组合。

  当 READ_COMMITTED_SNAPSHOT 数据库选项设置为 ON 并且满足以下条件之一时，无法指定 READPAST 表提示。

  • 会话的事务隔离级别为 READ COMMITTED。

  • 查询中也指定了 READCOMMITTED 表提示。

  若要在上述情况下指定 READPAST 提示，请删除 READCOMMITTED 表提示（如果存在），然后在查询中包括 READCOMMITTEDLOCK 表提示。

• READUNCOMMITTED
  指定允许脏读。不发布共享锁来阻止其他事务修改当前事务读取的数据，其他事务设置的排他锁不会阻碍当前事务读取锁定数据。允许脏读可能产生较多的并发操作，但其代价是读取以后会被其他事务回滚的数据修改。这可能会使您的事务出错，向用户显示从未提交过的数据，或者导致用户两次看到记录（或根本看不到记录）。有关脏读、不可重复读和幻读的详细信息，请参阅并发影响。

  READUNCOMMITTED 和 NOLOCK 提示仅适用于数据锁。所有查询（包括那些带有 READUNCOMMITTED 和 NOLOCK 提示的查询）都会在编译和执行过程中获取 Sch-S（架构稳定性）锁。因此，当并发事务持有表的 Sch-M（架构修改）锁时，将阻塞查询。例如，数据定义语言 (DDL) 操作在修改表的架构信息之前获取 Sch-M 锁。所有并发查询（包括那些使用 READUNCOMMITTED 或 NOLOCK 提示运行的查询）都会在尝试获取 Sch-S 锁时被阻塞。相反，持有 Sch-S 锁的查询将阻塞尝试获取 Sch-M 锁的并发事务。有关锁行为的详细信息，请参阅锁兼容性（数据库引擎）。

  不能为通过插入、更新或删除操作修改过的表指定 READUNCOMMITTED 和 NOLOCK。SQL Server 查询优化器忽略 FROM 子句中应用于 UPDATE 或 DELETE 语句的目标表的 READUNCOMMITTED 和 NOLOCK 提示。

  注意
  在 SQL Server 的未来版本中，将不再支持在 FROM 子句中使用应用于 UPDATE 或 DELETE 语句目标表的 READUNCOMMITTED 和 NOLOCK 提示。请避免在新的开发工作上下文中使用这些提示，并计划修改当前使用它们的应用程序。

  可以通过使用以下任意一种方法，在保护事务避免对未提交的数据修改进行脏读的同时最大程度地减少锁争用：

  • READ COMMITTED 隔离级别，其中 READ_COMMITTED_SNAPSHOT 数据库选项设置为 ON。

  • SNAPSHOT 隔离级别。

  有关隔离级别的详细信息，请参阅 SET TRANSACTION ISOLATION LEVEL (Transact-SQL)。

  注意
  如果在指定了 READUNCOMMITTED 的情况下收到 601 号错误消息，则按解决死锁错误 (1205) 的方法解决该错误，然后重试语句。

• REPEATABLEREAD
  指定事务在 REPEATABLE READ 隔离级别运行时，使用相同的锁定语义执行一次扫描。有关隔离级别的详细信息，请参阅 SET TRANSACTION ISOLATION LEVEL (Transact-SQL)。

• ROWLOCK
  指定通常采用页锁或表锁时，采用行锁。在从 SNAPSHOT 隔离级别操作的事务中指定时，除非将 ROWLOCK 与需要锁的其他表提示（例如，UPDLOCK 和 HOLDLOCK）组合，否则不会取得行锁。

• SERIALIZABLE
  等同于 HOLDLOCK。保持共享锁直到事务完成，使共享锁更具有限制性；而不是无论事务是否完成，都在不再需要所需表或数据页时立即释放共享锁。执行扫描时所用的语义与在 SERIALIZABLE 隔离级别运行的事务的语义相同。有关隔离级别的详细信息，请参阅 SET TRANSACTION ISOLATION LEVEL (Transact-SQL)。

• TABLOCK
  指定对表采用共享锁并让其一直持有，直至语句结束。如果同时指定了 HOLDLOCK，则会一直持有共享表锁，直至事务结束。

  在通过使用 INSERT INTO <target_table> SELECT <columns> FROM <source_table> 语句将数据导入某一堆时，可通过为目标表指定 TABLOCK 提示，实现语句的优化的日志记录和锁定。此外，数据库的恢复模式必须设置为简单或大容量日志模式。有关详细信息，请参阅 INSERT (Transact-SQL)。

  在与 OPENROWSET 大容量行集提供程序一起使用以将数据导入表的时候，TABLOCK 将启用多个客户端，以并发方式将数据加载到具有优化日志记录和锁定的目标表中。有关详细信息，请参阅在大容量导入中按最小方式记录日志的前提条件。

• TABLOCKX
  指定对表采用排他锁。

• UPDLOCK
  指定采用更新锁并保持到事务完成。

• XLOCK
  指定采用排他锁并保持到事务完成。如果同时指定了 ROWLOCK, PAGLOCK 或 TABLOCK，则排他锁将应用于相应的粒度级别。

注释

如果查询计划不访问表，则将忽略表提示。这可能是由于优化器选择了完全不访问该表，也可能是因为改成了访问索引视图。在后一种情况中，使用 OPTION (EXPAND VIEWS) 查询提示可阻止访问索引视图。

所有锁提示将传播到查询计划访问的所有表和视图，其中包括在视图中引用的表和视图。另外，SQL Server 还将执行对应的锁一致性检查。

获取行级别锁的锁提示 ROWLOCK、UPDLOCK 和 XLOCK 可能对索引键而不是实际的数据行采用锁。例如，如果表具有非聚集索引，而且由涵盖索引来处理使用锁提示的 SELECT 语句，则获得的锁针对的是涵盖索引中的索引键而不是基表中的数据行。

如果某个表包含计算列，而该计算列是由访问其他某些表中的列的表达式或函数计算的，则不对这些表使用表提示，即不传播表提示。例如，对查询中的表指定 NOLOCK 表提示。此表包含的计算列是由访问另一表中的列的表达式和函数组合计算的。表达式和函数引用的表在被访问时将不使用 NOLOCK 表提示。

对于 FROM 子句中的每个表，SQL Server 不允许存在多个来自以下各个组的表提示：

• 粒度提示：PAGLOCK、NOLOCK、ROWLOCK、TABLOCK 或 TABLOCKX。

• 隔离级别提示：HOLDLOCK、NOLOCK、READCOMMITTED、REPEATABLEREAD 和 SERIALIZABLE。

筛选索引提示

筛选索引可用作表提示，但如果它未将查询选择的所有行都包含在内，将导致查询优化器产生错误 8622。下面是一个无效筛选索引提示的示例。该示例创建了筛选索引 FIBillOfMaterialsWithComponentID，然后将其用作 SELECT 语句的索引提示。筛选索引谓词包含 ComponentID 为 533、324 和 753 的数据行。查询谓词也包含 ComponentID 为 533、324 和 753 的数据行，但它扩展了结果集，使之包含 ComponentID 为 855 和 924 的数据行，而筛选索引中则不包含这两行。因此，查询优化器无法使用此筛选索引提示，并产生错误 8622。有关详细信息，请参阅筛选索引设计准则。

USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsWithComponentID' 
    AND object_id = OBJECT_ID(N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithComponentID
    ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED INDEX "FIBillOfMaterialsWithComponentID"
    ON Production.BillOfMaterials (ComponentID, StartDate, EndDate)
    WHERE ComponentID IN (533, 324, 753);
GO
SELECT StartDate, ComponentID FROM Production.BillOfMaterials
    WITH( INDEX (FIBillOfMaterialsWithComponentID) )
    WHERE ComponentID in (533, 324, 753, 855, 924);
GO

如果 SET 选项不包含筛选索引所需的值，查询优化器将不考虑使用索引提示。有关详细信息，请参阅 CREATE INDEX (Transact-SQL)。

Using 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)。

权限

KEEPIDENTITY、IGNORE_CONSTRAINTS 和 IGNORE_TRIGGERS 提示需要具有对表的 ALTER 权限。

示例

A. 使用 TABLOCK 提示指定锁定方法

下面的示例指定对 Production.Product 表采用共享锁，并保持到 UPDATE 语句结束。

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

B. 使用 FORCESEEK 提示指定索引查找操作

下面的示例使用 FORCESEEK 提示强制查询优化器对 Sales.SalesOrderDetail 表执行索引查找操作。

USE AdventureWorks;
GO
SELECT *
FROM Sales.SalesOrderHeader AS h
INNER JOIN Sales.SalesOrderDetail AS d WITH (FORCESEEK)
    ON h.SalesOrderID = d.SalesOrderID 
WHERE h.TotalDue > 100
AND (d.OrderQty > 5 OR d.LineTotal < 1000.00);
GO

请参阅

参考

OPENROWSET (Transact-SQL)

提示 (Transact-SQL)

查询提示 (Transact-SQL)

概念

锁定提示

视图解析