• 發行項

資料表提示 (Transact-SQL)

資料表提示會在資料操作語言 (DML) 陳述式的持續時間內覆寫查詢最佳化工具的預設行為，其方式是指定鎖定方法、一個或多個索引、查詢處理作業 (例如資料表掃描或索引搜尋) 或是其他選項。資料表提示指定於 DML 陳述式的 FROM 子句中，而且只會影響該子句中參考的資料表或檢視表。

警告注意事項注意

由於 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 [( 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 
}

引數

  • WITH ( <table_hint> ) [ [ , ]...n]
    只有當資料表提示是使用 WITH 關鍵字來指定時，才支援資料表提示，但有某些例外。它必須用括號括住。

    重要事項重要事項

    省略 WITH 關鍵字是已被取代的功能，而且將在未來的 Microsoft SQL Server 版本中移除。請務必在新的開發工作中指定 WITH，並且修改目前省略此關鍵字的應用程式。

    用空格而非逗號來分隔提示是已被取代的功能，而且將在未來的 Microsoft SQL Server 版本中移除。請務必在新的開發工作中指定逗號，並且修改目前省略逗號的應用程式。

    不論是否有 WITH 關鍵字，都允許使用下列資料表提示：NOLOCK、READUNCOMMITTED、UPDLOCK、REPEATABLEREAD、SERIALIZABLE、READCOMMITTED、FASTFIRSTROW、TABLOCK、TABLOCKX、PAGLOCK、ROWLOCK、NOWAIT、READPAST、XLOCK 和 NOEXPAND。在沒有 WITH 關鍵字的情況下指定這些資料表提示時，應該單獨指定這些提示。例如，FROM t WITH (TABLOCK).。使用另一個選項來指定提示時，您必須利用 WITH 關鍵字來指定提示。例如，FROM t WITH (TABLOCK, INDEX(myindex))。

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

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

  • INDEX (index_value [,... n] ) | INDEX = (index_value)
    INDEX(index_value) 語法會指定查詢最佳化工具在處理陳述式時，所要使用之一個或多個索引的名稱或識別碼。替代的 INDEX = (index_value) 語法僅允許使用單一索引值。

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

    如果在單一提示清單中使用多個索引，便會忽略任何重複項目，而且會利用其餘列出的索引來擷取資料表的資料列。索引提示中的索引順序非常重要。另外，多個索引提示也會強制執行索引的 AND 作業，而且查詢最佳化工具會在所存取的每個索引上盡量套用多一點的條件。如果提示索引的集合不含此查詢所參考的所有資料行，則當 SQL Server 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 <columns> FROM OPENROWSET(BULK...) 陳述式中使用此提示的範例，請參閱＜在大量匯入期間保留 Null 或使用預設值＞。

  • FASTFIRSTROW
    這相當於 OPTION (FAST 1)。如需詳細資訊，請參閱＜查詢提示 (Transact-SQL)＞。

    重要事項重要事項

    下一版的 Microsoft SQL Server 將不再提供此功能。請勿在新的開發工作中使用此功能，並且儘速修改使用此功能的應用程式。

  • FORCESEEK [ **(index_value(**index_column_name [ ,...n ] )) ]
    指定查詢最佳化工具只使用索引搜尋作業，當做資料表或檢視表資料的存取路徑。從 SQL Server 2008 R2 SP1 開始，您也可以指定索引參數。使用索引參數來指定 FORCESEEK 類似於使用 FORCESEEK 搭配 INDEX 提示。不過，您可以同時指定要搜尋的索引以及要在搜尋作業中考慮的索引資料行，藉以更有效地控制查詢最佳化工具所使用的存取路徑。

    • index_value
      這是索引名稱或索引識別碼值。您無法指定索引識別碼 0 (堆積)。若要傳回索引名稱或識別碼，請查詢 sys.indexes 目錄檢視。

    • index_column_name
      這是要包含在搜尋作業中的索引資料行名稱。查詢最佳化工具只會透過至少使用指定之索引資料行的指定索引，考慮使用索引搜尋作業。最佳化工具可能會視需要考慮其他資料行。例如，如果指定了非叢集索引，最佳化工具可能會選擇使用叢集索引鍵資料行，以及指定的資料行。

    您可以使用下列方式來指定 FORCESEEK 提示。

    語法

    範例

    說明

    沒有索引或 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 提示。

    • 每個資料表或檢視表最多可套用提示一次。

    • 您無法針對遠端資料來源指定提示。使用索引提示來指定 FORCESEEK 時，系統會傳回錯誤 7377，而不搭配索引提示使用 FORCESEEK 時，則會傳回錯誤 8180。

    • 如果 FORCESEEK 造成找不到任何計畫，將會傳回錯誤 8622。

    使用索引參數來指定 FORCESEEK 時，則適用下列指導方針和限制。

    • 您無法結合 INDEX 提示或其他 FORCESEEK 提示來指定提示。

    • 至少必須指定一個資料行，而且它必須是前端索引鍵資料行。

    • 您可以指定其他索引資料行，不過無法略過索引鍵資料行。例如，如果指定的索引包含索引鍵資料行 a、b 和 c，有效的語法就會包括 FORCESEEK (MyIndex (a)) 和 FORCESEEK (MyIndex (a, b)。無效的語法則包括 FORCESEEK (MyIndex (c)) 和 FORCESEEK (MyIndex (a, c)。

    • 在提示中指定的資料行名稱順序必須符合參考索引中的資料行順序。

    • 您無法指定不在索引鍵定義中的資料行。例如，在非叢集索引中，您只能指定已定義的索引鍵資料行。您無法指定自動包含在索引中的叢集索引鍵資料行，但是最佳化工具可以使用它們。

    • 修改索引定義 (例如，加入或移除資料行) 可能需要修改參考該索引的查詢。

    • 提示會讓最佳化工具無法考慮資料表的任何空間或 XML 索引。

    • 您無法結合 FORCESCAN 提示來指定提示。

    • 若為資料分割索引，您就無法在 FORCESEEK 提示中指定 SQL Server 隱含加入的資料分割資料行。如需詳細資訊，請參閱＜資料分割索引的特殊指導方針＞。

    警告注意事項注意

    使用索引參數來指定 FORCESEEK 會限制最佳化工具可考慮的計畫數目，其限制幅度超過不使用參數來指定 FORCESEEK 的限制。在其他情況下，這可能會導致系統發生「無法產生計畫」錯誤。在未來的版本中，最佳化工具的內部修改可能會允許考慮更多計畫。如需詳細資訊，請參閱＜使用 FORCESEEK 資料表提示＞。

  • FORCESCAN
    在 SQL Server 2008 R2 SP1 中導入的這個提示會指定查詢最佳化工具只使用索引掃描作業，當做參考之資料表或檢視表的存取路徑。在最佳化工具低估受影響資料列的數目並且選擇搜尋作業而非掃描作業的查詢中，FORCESCAN 提示可能很有用。發生這種情況時，授與作業的記憶體數量太少，而且查詢效能會受到影響。

    您可以使用或不使用 INDEX 提示來指定 FORCESCAN。與索引提示結合 (INDEX = index_name, FORCESCAN) 時，查詢最佳化工具只會透過指定的索引，考慮使用掃描存取路徑來存取參考的資料表。您可以使用索引提示 INDEX(0) 來指定 FORCESCAN，針對基底資料表強制執行資料表掃描作業。

    若為資料分割資料表和索引，在透過查詢述詞評估刪除資料分割之後，才會套用 FORCESCAN。這表示，掃描只會套用至其餘資料分割，而非整份資料表。

    FORCESCAN 提示具有下列限制。

    • 您無法針對 INSERT、UPDATE 或 DELETE 陳述式的目標資料表指定提示。

    • 提示無法搭配多個索引提示使用。

    • 提示會讓最佳化工具無法考慮資料表的任何空間或 XML 索引。

    • 您無法針對遠端資料來源指定提示。

    • 您無法結合 FORCESEEK 提示來指定提示。

  • 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。

    [!附註]

    如果是 UPDATE 或 DELETE 陳述式：未來的 Microsoft SQL Server 版本將移除這項功能。請避免在新的開發工作中使用這項功能，並規劃修改目前使用這項功能的應用程式。

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

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

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

    [!附註]

    如果是 UPDATE 或 DELETE 陳述式：未來的 Microsoft SQL Server 版本將移除這項功能。請避免在新的開發工作中使用這項功能，並規劃修改目前使用這項功能的應用程式。

  • READCOMMITTEDLOCK
    指定讀取作業使用鎖定，以遵守 READ COMMITTED 隔離等級的規則。不論資料庫選項 READ_COMMITTED_SNAPSHOT 的設定為何，Database Engine 一律在讀取資料時取得共用鎖定，並在讀取作業完成時釋放這些鎖定。如需有關隔離等級的詳細資訊，請參閱＜SET TRANSACTION ISOLATION LEVEL (Transact-SQL)＞。您無法針對 INSERT 陳述式的目標資料表指定這個提示，否則會傳回錯誤 4140。

  • READPAST
    指定 Database Engine 不讀取其他交易已鎖定的資料列。指定了 READPAST 時，將會略過資料列層級鎖定。也就是說，Database Engine 會略過資料列，而不是封鎖目前交易，直到釋放鎖定為止。例如，假設資料表 T1 包含了值為 1、2、3、4、5 的單一整數資料行。如果交易 A 將 3 的值變更為 8，但是尚未認可，則 SELECT * FROM T1 (READPAST) 會產生 1、2、4、5 的值。READPAST 主要是在實作一個工作佇列來使用 SQL Server 資料表時，為了減少鎖定爭用而使用。使用 READPAST 的佇列讀取器會略過其他交易已鎖定的佇列項目，直接到下一個可用的佇列項目，不需要等待其他交易釋放鎖定。

    UPDATE 或 DELETE 陳述式所參考的任何資料表，以及 FROM 子句所參考的任何資料表，都可以指定 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 鎖定的並行交易。如需有關鎖定行為的詳細資訊，請參閱＜鎖定相容性 (Database Engine)＞。

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

    [!附註]

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

    您可以使用下列其中一個選項，防止交易讀到尚未認可的資料修改 (中途讀取)，同時也將鎖定爭用的情況減到最低：

    • 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
    指定在資料表層級套用取得的鎖定。取得的鎖定類型主要取決於執行的陳述式。例如，SELECT 陳述式可能會取得共用鎖定。透過指定 TABLOCK，共用鎖定就會套用至整份資料表，而非在資料列或頁面層級套用。如果同時指定了 HOLDLOCK，就會將資料表鎖定保留到交易結束為止。

    當您使用 INSERT INTO <target_table> SELECT <columns> FROM <source_table> 陳述式，將資料匯入堆積時，可以指定目標資料表的 TABLOCK 提示來啟用陳述式的最佳化記錄和鎖定。此外，資料庫的復原模式必須設定為簡單或大量記錄。如需詳細資訊，請參閱＜INSERT (Transact-SQL)＞。

    當 TABLOCK 搭配 OPENROWSET BULK 資料列集提供者使用，以便將資料匯入資料表時，它會使多個用戶端能夠以最佳化的記錄和鎖定，同時將資料載入目標資料表中。如需詳細資訊，請參閱＜大量匯入採用最低限度記錄的必要條件＞。

  • 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、AND 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 的資料列。此查詢述詞也包含 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 只適用於索引檢視表。索引檢視表是建立了唯一叢集索引的檢視表。如果查詢包含同時在索引檢視表和基底資料表中的資料行參考，而且查詢最佳化工具判斷使用索引檢視表能夠提供最好的查詢執行方法，則查詢最佳化工具會使用檢視表的索引。這項功能稱為「索引檢視表比對」(Indexed View Matching)，只有 SQL Server 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 在 FROM 子句並未直接指定檢視表名稱的查詢中，不會提供強制使用特定索引檢視表的提示；不過，查詢最佳化工具仍會考慮使用索引檢視表，即使查詢並未直接參考它們，也是如此。

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

將資料表提示當做查詢提示使用

也可以使用 OPTION (TABLE HINT) 子句將資料表提示指定為查詢提示。建議您只有在計畫指南的內容中，才將資料表提示當做查詢提示使用。如果是特定的查詢，只將這些提示指定為資料表提示。如需詳細資訊，請參閱＜查詢提示 (Transact-SQL)＞。

權限

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

範例

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