資料表提示 (Transact-SQL)

更新： 2006 年 12 月 12 日

指定查詢最佳化工具搭配這份資料表或檢視來使用資料表掃描、一或多個索引或鎖定方法，或將它們用在這個 SELECT、INSERT、UPDATE 或 DELETE 陳述式上。雖然這是一個選項，但查詢最佳化工具通常仍可以在未指定提示的情況下，取用最好的最佳化方法。

重要事項：
由於 SQL Server 2005 查詢最佳化工具通常會選取最好的查詢執行計劃，因此，我們建議資深的開發人員和資料庫管理員，盡量不要使用提示 (包括 <table_hint>)。

適用於：

DELETE

INSERT

SELECT

UPDATE

Transact-SQL 語法慣例

語法

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

<table_hint_limited> ::=
{
    KEEPIDENTITY 
  | KEEPDEFAULTS 
  | FASTFIRSTROW 
  | HOLDLOCK 
  | IGNORE_CONSTRAINTS 
  | IGNORE_TRIGGERS 
  | NOWAIT
    | PAGLOCK 
  | READCOMMITTED 
  | READCOMMITTEDLOCK 
  | READPAST 
  | REPEATABLEREAD 
  | ROWLOCK 
  | SERIALIZABLE 
  | TABLOCK 
  | TABLOCKX 
  | UPDLOCK 
  | XLOCK 
} 

引數

• NOEXPAND
  指定當查詢最佳化工具處理查詢時，不展開任何索引檢視來存取基礎資料表。查詢最佳化工具會依照含叢集索引之資料表的方式來處理檢視。NOEXPAND 只適用於索引檢視。如需詳細資訊，請參閱＜備註＞一節。

• INDEX ( index_val [ ,... n ] )
  指定查詢最佳化工具處理陳述式時，所用之索引的名稱或識別碼。每份資料表只能指定一個索引提示。

  如果叢集索引存在，INDEX(0) 會強制執行叢集索引掃描，INDEX(1) 會強制執行叢集索引掃描或搜尋。如果沒有叢集索引，INDEX(0) 會強制執行資料表掃描，INDEX(1) 會解譯為一則錯誤。

  替代的 INDEX = 語法指定單一索引提示。支援這個項目的目的，只是為了與舊版相容。

  如果在單一提示清單中使用多個索引，便會忽略複本，且會利用其餘列出的索引來擷取資料表的資料列。索引提示中的索引順序非常重要。另外，多個索引提示也會強制執行索引的 AND 作業，查詢最佳化工具會在所存取的每個索引上套用盡可能多的條件。如果提示索引集合的涵蓋面不足，就會在 SQL Server 2005 Database Engine 擷取所有索引資料行之後進行提取。

  附註：
  當星狀聯結的事實資料表使用參考多個索引的索引提示時，最佳化工具會忽略索引提示，且會傳回一則警告訊息。另外，指定了索引提示的資料表不接受索引的 OR 作業。

  資料表提示中的最大索引數目是 250 個非叢集索引。

• KEEPIDENTITY
  只有在搭配 OPENROWSET 使用 BULK 選項時，才適用於 INSERT 陳述式。

  指定識別資料行要使用匯入之資料檔中的一或多個識別值。如果未指定 KEEPIDENTITY，就會驗證這個資料行的識別值，但不會匯入它，查詢最佳化工具會根據建立資料表期間所指定的種子值和遞增值來自動指派唯一值。

  重要事項：
  如果資料檔中沒有資料表或檢視中之識別資料行的值，除非識別資料行是資料表的最後一個資料行，否則，您必須跳過識別資料行。如需詳細資訊，請參閱＜使用格式檔案以略過資料欄位＞。如果順利跳過識別資料行，查詢最佳化工具會自動在匯入資料表資料列中，指派識別資料行的唯一值。

  如需在 INSERT ...SELECT * FROM OPENROWSET(BULK...) 陳述式中使用此提示的範例，請參閱＜大量匯入資料時保留識別值＞。

  如需有關檢查資料表識別值的詳細資訊，請參閱＜DBCC CHECKIDENT (Transact-SQL)＞。

• KEEPDEFAULTS
  只有在搭配 OPENROWSET 使用 BULK 選項時，才適用於 INSERT 陳述式。

  指定在資料記錄缺少資料行的值時，如果資料表資料行有預設值的話，便插入這個預設值，而不是 NULL。

  如需在 INSERT ...SELECT * FROM OPENROWSET(BULK...) 陳述式中使用此提示的範例，請參閱＜在大量匯入期間保留 Null 或使用預設值＞。

• FASTFIRSTROW
  這相當於 OPTION (FAST 1)。如需詳細資訊，請參閱＜SELECT＞之 OPTION 子句中的 FAST。

• HOLDLOCK
  這相當於 SERIALIZABLE。如需詳細資訊，請參閱這個主題稍後的 SERIALIZABLE。HOLDLOCK 只適用於指定了 HOLDLOCK 的資料表或檢視，且只在使用它的陳述式所定義的交易持續時間內有效。包括 FOR BROWSE 選項的 SELECT 陳述式不能使用 HOLDLOCK。

• IGNORE_CONSTRAINTS
  只有在搭配 OPENROWSET 使用 BULK 選項時，才適用於 INSERT 陳述式。

  指定大量匯入作業忽略資料表的任何條件約束。根據預設，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
  只有在搭配 OPENROWSET 使用 BULK 選項時，才適用於 INSERT 陳述式。

  指定大量匯入作業忽略資料表所定義的任何觸發程序。依預設，INSERT 會套用觸發程序。

  請只在應用程式不相依於任何觸發程序，且發揮最大效能非常重要時，才使用 IGNORE_TRIGGERS。

• NOLOCK
  這相當於 READUNCOMMITTED。如需詳細資訊，請參閱這個主題稍後的 READUNCOMMITTED。

• NOWAIT
  指示 SQL Server 2005 Database Engine 發現資料表的鎖定時，便立刻傳回訊息。NOWAIT 相當於針對特定資料表指定 SET LOCK_TIMEOUT 0。

• PAGLOCK
  在通常會採用資料列或索引鍵的個別鎖定，或通常會採用單一資料表鎖定的情況下，頁面會鎖定。依預設，會使用作業所適用的鎖定模式。如果是在以 SNAPSHOT 隔離等級操作的交易中指定時，不會採用頁面鎖定，除非 PAGLOCK 是與其他需要鎖定的資料表提示相結合，例如 UPDLOCK 和 HOLDLOCK。

• READCOMMITTED
  指定讀取作業使用鎖定或資料列版本控制，以遵守 READ COMMITTED 隔離等級的規則。如果資料庫選項 READ_COMITTED_SNAPSHOT 是 OFF，且 Database Engine 在讀取資料時取得共用鎖定，在讀取作業完成時釋放這些鎖定。如果資料庫選項 READ_COMMITTED_SNAPSHOT 是 ON，Database Engine 便不需要鎖定，它會使用資料列版本控制。如需有關隔離等級的詳細資訊，請參閱＜SET TRANSACTION ISOLATION LEVEL (Transact-SQL)＞。

• READCOMMITTEDLOCK
  指定讀取作業使用鎖定，以遵守 READ COMMITTED 隔離等級的規則。不論資料庫選項 READ_COMMITTED_SNAPSHOT 的設定為何，Database Engine 一律在讀取資料時取得共用鎖定，在讀取作業完成時釋放這些鎖定。如需有關隔離等級的詳細資訊，請參閱＜SET TRANSACTION ISOLATION LEVEL (Transact-SQL)＞。

• READPAST
  指定 Database Engine 不讀取其他交易已鎖定的資料列。在大部分情況下，這項指定也適用於頁面。Database Engine 會跳過資料列或頁面，而不是封鎖目前交易，直到釋放鎖定為止。您只能在執行 READ COMMITTED 或 REPEATABLE READ 隔離等級的交易中指定 READPAST。如果是在以 SNAPSHOT 隔離等級操作的交易中指定時，READPAST 必須與其他需要鎖定的資料表提示相結合，例如 UPDLOCK 和 HOLDLOCK。當指定 READPAST 時，會略過資料列層級和頁面層級的鎖定。UPDATE 或 DELETE 陳述式所參考的任何資料表，以及 FROM 子句所參考的任何資料表，都可以指定 READPAST。當在 UPDATE 陳述式中指定 READPAST 時，只有在讀取資料來識別要更新的記錄時，才會套用 READPAST，不論是在陳述式的哪個位置指定，都是如此。INSERT 陳述式 INTO 子句中的資料表不能指定 READPAST。

  使用 READPAST 的讀取作業不會進行封鎖。當讀取外部索引鍵或索引檢視時，或當修改次要索引時，使用 READPAST 的更新或刪除作業可能會進行封鎖。

  例如，假設 T1 資料表包含其值為 1、2、3、4、5 的單一整數資料行。如果交易 A 將值 3 改成 8，但尚未被認可，SELECT * FROM T1 (READPAST) 便會產生 1、2、4、5 等值。READPAST 主要是在實作使用 SQL Server 資料表的工作佇列時，用來減少鎖定爭用的情況。使用 READPAST 的佇列讀取器會略過其他交易已鎖定的佇列項目，直接到下一個可用的佇列項目，不需要等待其他交易釋放鎖定。

• READUNCOMMITTED
  指定允許中途讀取。不會發出任何共用鎖定來防止其他交易修改目前交易所讀取的資料，其他交易所設定的獨佔鎖定也不會封鎖目前交易，使它無法讀取鎖定的資料。允許中途讀取可以提高並行程度，但代價是所讀取的資料修改後來會被其他交易回復。這可能會使您的交易發生錯誤，或提供使用者永遠不被認可的資料。

  READUNCOMMITTED 和 NOLOCK 提示只適用於資料鎖定。所有的查詢，包括具有 READUNCOMMITTED 和 NOLOCK 提示的查詢，都會在編譯和執行期間取得 Sch-S (結構描述固定性) 鎖定。因此，當並行交易在資料表上保有 Sch-M (結構描述修改) 鎖定時，查詢將會被封鎖。例如，資料定義語言 (DDL) 作業會在修改資料表的結構描述資訊之前先取得 Sch-M 鎖定。任何並行查詢，包括以 READUNCOMMITTED 或 NOLOCK 提示執行的查詢，會在嘗試取得 Sch-S 鎖定時遭到封鎖。相反地，保有 Sch-S 鎖定的查詢將會封鎖嘗試取得 Sch-M 鎖定的並行交易。如需有關鎖定行為的詳細資訊，請參閱＜鎖定相容性 (Database Engine)＞。

  無法針對插入、更新或刪除作業修改的資料表指定 READUNCOMMITTED 和 NOLOCK。SQL Server 查詢最佳化工具會忽略套用在 UPDATE 或 DELETE 陳述式目標資料表的 FROM 子句中的 READUNCOMMITTED 和 NOLOCK 提示。

  附註：
  Microsoft SQL Server 的未來版本將移除套用在 UPDATE 或 DELETE 陳述式目標資料表的 FROM 子句中 READUNCOMMITTED 和 NOLOCK 提示的使用支援。請避免在新的開發工作中使用此內容中的這些提示，並規劃修改目前在使用這些提示的應用程式。

  在 SQL Server 2005 中，您可以利用下列兩個等級之一，既防止交易讀到尚未認可的資料修改 (中途讀取)，同時也將鎖定爭用的情況減到最低：

  • READ_COMMITTED_SNAPSHOT 資料庫選項設為 ON 的 READ COMMITTED 隔離等級。
  • 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，就會將共用資料表鎖定保留到交易結束為止。

  當用來搭配使用 OPENROWSET 大量資料列集提供者，以便將資料匯入不含索引的資料表時，TABLOCK 會使多個用戶端能夠以最佳化的記錄和鎖定，同時將資料載入目標資料表中。

• TABLOCKX
  指定資料表設定獨佔鎖定，直到交易完成為止。

• UPDLOCK
  指定採用更新鎖定，且保留到交易完成為止。

• XLOCK
  指定採用獨佔鎖定，且保留到交易完成為止。如果指定了 ROWLOCK、PAGLOCK 或 TABLOCK，就會將獨佔鎖定套用在適當的資料粒度層級上。

備註

如果查詢計劃並未存取資料表，就會忽略資料表提示。這可能是最佳化工具選擇完全不存取資料表所造成的，也可能是因為改成存取索引檢視。在後面一種情況中，您可以利用 OPTION (EXPAND VIEWS) 查詢提示來防止存取索引檢視。

在資料表提示之間使用逗號是選擇性的，但建議您最好這麼做。支援用空格而不用逗號來分隔提示的目的，是為了與舊版相容。

在 SQL Server 2005 中，除了部分例外狀況，只有在利用 WITH 關鍵字來指定提示時，FROM 子句才支援資料表提示。資料表提示也必須用括號來指定。

不論是否有 WITH 關鍵字，都允許使用下列資料表提示：NOLOCK、READUNCOMMITTED、UPDLOCK、REPEATABLEREAD、SERIALIZABLE、READCOMMITTED、FASTFIRSTROW、TABLOCK、TABLOCKX、PAGLOCK、ROWLOCK、NOWAIT、READPAST、XLOCK 和 NOEXPAND。在沒有 WITH 關鍵字的情況下指定這些資料表提示時，應該單獨指定這些提示。例如：

FROM t (FASTFIRSTROW)

如果要同時指定提示和其他選項，您必須利用 WITH 關鍵字來指定提示：

FROM t WITH (FASTFIRSTROW, INDEX(myindex))

當利用提示來查詢相容性層級是 90 的資料庫時，適用這些限制。

在 SQL Server 2005 中，所有鎖定提示都會傳播到檢視所參考的所有資料表和檢視中。另外，SQL Server 也會執行對應的鎖定一致性檢查。

取得資料列層級鎖定的 ROWLOCK、UPDLOCK、AND XLOCK 等鎖定提示，會將鎖定放在索引鍵上，而不是實際的資料列。例如，如果資料表有非叢集索引，且使用鎖定提示的 SELECT 陳述式由涵蓋索引來處理，就會取得涵蓋索引之索引鍵的鎖定，而不是基底資料表之資料列的鎖定。

如果資料表包含計算資料行，且計算資料行是由存取其他資料表之資料行的運算式或函數來計算，就不會在這些資料表上使用資料表提示。這表示不會傳播資料表提示。例如，在查詢中指定了資料表的 NOLOCK 資料表提示。這份資料表擁有多個計算資料行，這些計算資料行會利用存取另一資料表中之資料行的運算式和函數的組合來進行計算。當存取運算式和函數所參考的資料表時，它們不會使用 NOLOCK 資料表提示。

SQL Server 不允許 FROM 子句中每份資料表的下列每個群組，各多出一項資料表提示：

• 資料粒度提示：PAGLOCK、NOLOCK、ROWLOCK、TABLOCK 或 TABLOCKX。
• 隔離等級提示：HOLDLOCK、NOLOCK、READCOMMITTED、REPEATABLEREAD、SERIALIZABLE。

使用 NOEXPAND

NOEXPAND 只適用於索引檢視。索引檢視是建立了唯一叢集索引的檢視。如果查詢包含同時在索引檢視和基底資料表中的資料行參考，查詢最佳化工具會判斷使用索引檢視能夠提供最好的查詢執行方法，查詢最佳化工具會使用檢視的索引。這項功能稱為*「索引檢視比對」*，只有 SQL Server 2005 Enterprise 和 Developer 版本支援這項功能。

不過，若要使最佳化工具考慮比對索引檢視，或使用 NOEXPAND 提示所參考的索引檢視，下列 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 選項。只有在查詢也指定了檢視的名稱時，才能使用這個提示。SQL Server 2005 不提供在 FROM 子句並未直接指定檢視名稱的查詢中，強制使用特定索引檢視的提示；不過，查詢最佳化工具仍會考慮使用索引檢視，即使查詢並未直接參考它們，也是如此。

如需詳細資訊，請參閱＜解析檢視上的索引＞。

權限

KEEPIDENTITY、IGNORE_CONSTRAINTS 和 IGNORE_TRIGGERS 提示需要資料表的 ALTER 權限。

範例

下列範例指定在 Production.Product 資料表上採用的共用鎖定，且將鎖定保留到 UPDATE 陳述式結束為止。

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

請參閱

參考

OPENROWSET (Transact-SQL)
提示 (Transact-SQL)

其他資源

鎖定提示
檢視解析

說明及資訊

取得 SQL Server 2005 協助

變更歷程記錄

版本	歷程記錄
2006 年 12 月 12 日	變更的內容： 釐清 TABLOCK 提示設定的鎖定類型。 修訂 IGNORE_CONSTRAINTS 提示的描述，指出這會導致同時忽略 CHECK 和 FOREIGN KEY 條件約束。
2006 年 4 月 14 日	新增內容： 新增有關在以 SNAPSHOT 隔離等級操作的交易中使用 PAGLOCK、READPAST 和 ROWLOCK 的詳細資訊。
2005 年 12 月 5 日	變更的內容： 更新有關 READUNCOMMITTED 鎖定提示的資訊。