MERGE (Transact-SQL)

適用于:SQL Server (資料庫Azure Synapse分析) 支援的所有版本 Azure SQL

從與來源資料表聯結的結果,在目標資料表上執行插入、更新或刪除作業。 例如,根據在另一個資料表中所找到的差異在資料表中插入、更新或刪除資料列,以同步處理兩個資料表。

注意

針對 [Azure Synapse Analytics] 特定的 MERGE 特定重要內容,變更產品版本選取器。 若要將檔版本變更為 Azure Synapse Analytics:Azure Synapse Analytics

效能提示: 當兩個資料表有複雜的比對的特性時,MERGE 陳述式的條件式行為表現最佳。 例如沒有資料列時插入資料列,或資料列相符時更新資料列。 只要根據另一個資料表的資料列更新資料表,就能以基本 INSERT、UPDATE 及 DELETE 陳述式來改善效能及延展性。 例如:

INSERT tbl_A (col, col2)  
SELECT col, col2
FROM tbl_B
WHERE NOT EXISTS (SELECT col FROM tbl_A A2 WHERE A2.col = tbl_B.col);  

注意

SYNapse 專用 SQL 集區中現在已正式推出,版本為 '10.0.17829.0' 或更新版本。 (先前稱為 SQL DW) 並執行 SELECT @@VERSION ,連線到專用 SQL 集區。 可能需要暫停和繼續,以確保您的實例取得最新版本。

提示

當兩個資料表有複雜的比對的特性時,MERGE 陳述式的條件式行為表現最佳。 例如沒有資料列時插入資料列,或資料列相符時更新資料列。 只要根據另一個資料表的資料列更新一個資料表時,請考慮使用基本 INSERT、UPDATE 和 DELETE 子句,以提升查詢效能和延展性。 例如:

INSERT tbl_A (col, col2)  
SELECT col, col2
FROM tbl_B
WHERE NOT EXISTS (SELECT col FROM tbl_A A2 WHERE A2.col = tbl_B.col);  

主題連結圖示Transact-SQL 語法慣例

Syntax


-- SQL Server and Azure SQL Database
[ WITH <common_table_expression> [,...n] ]  
MERGE
    [ TOP ( expression ) [ PERCENT ] ]
    [ INTO ] <target_table> [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias ]  
    USING <table_source> [ [ AS ] table_alias ]
    ON <merge_search_condition>  
    [ WHEN MATCHED [ AND <clause_search_condition> ]  
        THEN <merge_matched> ] [ ...n ]  
    [ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]  
        THEN <merge_not_matched> ]  
    [ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]  
        THEN <merge_matched> ] [ ...n ]  
    [ <output_clause> ]  
    [ OPTION ( <query_hint> [ ,...n ] ) ]
;  

<target_table> ::=  
{
    [ database_name . schema_name . | schema_name . ]  
  target_table  
}  

<merge_hint>::=  
{  
    { [ <table_hint_limited> [ ,...n ] ]  
    [ [ , ] INDEX ( index_val [ ,...n ] ) ] }  
}  

<merge_search_condition> ::=  
    <search_condition>  

<merge_matched>::=  
    { UPDATE SET <set_clause> | DELETE }  

<merge_not_matched>::=  
{  
    INSERT [ ( column_list ) ]
        { VALUES ( values_list )  
        | DEFAULT VALUES }  
}  

<clause_search_condition> ::=  
    <search_condition> 

注意

若要檢視 SQL Server 2014 與更早版本的 Transact-SQL 語法,請參閱舊版文件

-- MERGE for Azure Synapse Analytics 
[ WITH <common_table_expression> [,...n] ]  
MERGE
    [ INTO ] <target_table> [ [ AS ] table_alias ]  
    USING <table_source> [ [ AS ] table_alias ]
    ON <merge_search_condition>  
    [ WHEN MATCHED [ AND <clause_search_condition> ]  
        THEN <merge_matched> ] [ ...n ]  
    [ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]  
        THEN <merge_not_matched> ]  
    [ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]  
        THEN <merge_matched> ] [ ...n ]
    [ OPTION ( <query_hint> [ ,...n ] ) ]
;  -- The semi-colon is required, or the query will return a syntax error. 

<target_table> ::=  
{
    [ database_name . schema_name . | schema_name . ]  
  target_table  
}  

<merge_search_condition> ::=  
    <search_condition>  

<merge_matched>::=  
    { UPDATE SET <set_clause> | DELETE }  

<merge_not_matched>::=  
{  
    INSERT [ ( column_list ) ]
        VALUES ( values_list )  
}  

<clause_search_condition> ::=  
    <search_condition> 

引數

WITH <common_table_expression>

指定在 MERGE 陳述式範圍內定義的暫存具名結果集或檢視表,也稱為通用資料表運算式。 結果集是從簡單查詢衍生而來,由 MERGE 陳述式參考。 如需詳細資訊,請參閱 WITH common_table_expression (Transact-SQL)

TOP (運算式* ) [ PERCENT ]

指定受到影響的資料列數目或百分比。 expression 可以是一個數字,也可以是資料列的百分比。 TOP 運算式中參考的資料列不會依任何順序排列。 如需詳細資訊,請參閱 TOP (Transact-SQL)

TOP 子句會套用至整個來源資料表和整個目標資料表聯結,以及不符合插入、更新或刪除動作資格的聯結資料列之後。 TOP 子句會進一步將聯結資料列數目減少至指定的值。 Insert、update 或 delete 動作會以未排序的方式套用至剩餘聯結資料列中。 亦即,將資料列散發到 WHEN 子句中定義的動作時,沒有任何特定順序。 例如,指定 TOP (10) 會影響 10 個資料列。 在這些資料列中,可能會更新 7 個及插入 3 個,或者可能會刪除 1 個、更新 5 個及插入 4 個,依此類推。

如果沒有來源資料表上的篩選,MERGE 語句可能會在來源資料表上執行資料表掃描或叢集索引掃描,以及目標資料表的資料表掃描或叢集索引掃描。 因此,即使使用 TOP 子句來藉由建立多個批次來修改大型資料表,I/O 效能有時也會受到影響。 在此狀況中,請務必確保所有後續批次都以新的資料列為目標。

database_name

target_table 所在的資料庫名稱。

schema_name

target_table 所屬的結構描述名稱。

target_table

根據clause_search_condition比 < 對來自 < table_source >> 之資料列的資料表或檢視表。 target_table 是 MERGE 陳述式的 WHEN 子句所指定之任何插入、更新或刪除作業的目標。

如果 target_table 是檢視,則對其進行的任何動作都必須滿足更新檢視的條件。 如需詳細資訊,請參閱透過檢視修改資料

target_table 不能是遠端資料表。 target_table 不能有任何定義的規則。

提示可以指定為 < merge_hint > 。

請注意,Azure Synapse Analytics 不支援merge_hints。

[ AS ] table_alias

用於參考 target_table 資料表的替代名稱。

使用 < table_source>

根據merge_search條件 > ,指定與target_table< 中的資料列相符的資料來源。 此項比對的結果會指定 MERGE 陳述式的 WHEN 子句所採取的動作。 <>table_source可以是遠端資料表或存取遠端資料表的衍生資料表。

<table_source > 可以是使用 Transact-SQL 資料表值建構 函式的衍生資料表,藉由指定多個資料列來建構資料表。

<>table_source可以是衍生資料表,用來 SELECT ... UNION ALL 藉由指定多個資料列來建構資料表。

[ AS ] table_alias

用於參考 table_source 資料表的替代名稱。

如需這個子句語法和引數的詳細資訊,請參閱 FROM (Transact-SQL)

ON < merge_search_condition>

指定table_source > 與target_table聯結的條件 < ,以判斷其相符之處。

警告

請務必只從目標資料表指定用於比對用途的資料行。 也就是說,從目標資料表中指定要與來源資料表的對應資料行進行比較的資料行。 請勿嘗試在 ON 子句中篩選出目標資料表的資料列 (例如指定 AND NOT target_table.column_x = value) 來改善查詢效能。 這樣做可能會傳回非預期且不正確的結果。

比對時 < merge_matched>

指定 *target_table的所有資料列符合 table_source ON merge_search_condition 傳 < 回的資料列,並滿足任何其他搜尋條件,都會根據 < merge_matched > 子句來更新 > 或刪除。 <>

MERGE 陳述式最多可以具有兩個 WHEN MATCHED 子句。 如果指定了兩個子句,則第一個子句必須伴隨 AND < search_condition > 子句。 對於任何指定的資料列,只有第一個 WHEN MATCHED 子句未套用時,才會套用第二個 WHEN MATCHED 子句。 如果有兩個 WHEN MATCHED 子句,則一個必須指定 UPDATE 動作,另一個則必須指定 DELETE 動作。 在 merge_matched > 子句中 < 指定 UPDATE 時,table_source的多個資料列 <> 會根據 <> merge_search_condition比對target_table中的資料列,SQL Server會傳回錯誤。 MERGE 陳述式無法更新同一資料列一次以上或更新及刪除同一資料列。

當不符合 [依目標] 時,則 < merge_not_matched>

指定table_source ON <> 所 <> 傳回之每個資料列的資料列插入target_table merge_search_condition不符合target_table中的資料列,但如果存在,則會滿足額外的搜尋條件。 要插入的值是由 < merge_not_matched > 子句所指定。 MERGE 陳述式只能具有一個 WHEN NOT MATCHED [ BY TARGET ] 子句。

當來源不相符時 < ,merge_matched>

指定 *target_table的所有資料列不符合 table_source ON merge_search_condition 傳 < 回的資料列,以及滿足任何其他搜尋條件的資料列,都會根據 < merge_matched > 子句來更新 > 或刪除。 <>

MERGE 陳述式最多可以具有兩個 WHEN NOT MATCHED BY SOURCE 子句。 如果指定了兩個子句,則第一個子句必須伴隨 AND < clause_search_condition > 子句。 對於任何指定的資料列,只有第一個 WHEN NOT MATCHED BY SOURCE 子句未套用時,才會套用第二個 WHEN NOT MATCHED BY SOURCE 子句。 如果有兩個 WHEN NOT MATCHED BY SOURCE 子句,則一個必須指定 UPDATE 動作,另一個則必須指定 DELETE 動作。 只有目標資料表中的資料行可以在clause_search_condition > 中 < 參考。

當table_source未傳 < 回任何 > 資料列時,就無法存取來源資料表中的資料行。 如果merge_matched > 子句中指定的 < 更新或刪除動作參考來源資料表中的資料行,則會傳回錯誤 207 (不正確資料行名稱) 。 例如,子句 WHEN NOT MATCHED BY SOURCE THEN UPDATE SET TargetTable.Col1 = SourceTable.Col1 可能會導致陳述式失敗,因為無法存取來源資料表的 Col1

AND < clause_search_condition>

指定任何有效的搜尋條件。 如需詳細資訊,請參閱 搜尋條件 (Transact-SQL)

<table_hint_limited>

指定針對每個由 MERGE 陳述式所執行的插入、更新或刪除動作,套用到目標資料表的一或多個資料表提示。 WITH 關鍵字和括號都是必要的。

不允許使用 NOLOCK 和 READUNCOMMITTED。 如需資料表提示的詳細資訊,請參閱 Transact-SQL) (資料表提示

指定 INSERT 陳述式目標資料表之 TABLOCK 提示的效果,與指定 TABLOCKX 提示相同。 獨佔鎖定是在資料表上取得的。 當指定 FORCESEEK 時,該提示會套用到與來源資料表聯結之目標資料表的隱含執行個體。

警告

使用 WHEN NOT MATCHED [ BY TARGET ] THEN INSERT 指定 READPAST 可能會導致違反 UNIQUE 條件約束的 INSERT 作業。

INDEX ( index_val [ ,...n ] )

在目標資料表上指定一或多個索引的名稱或識別碼,以用於與來源資料表執行隱含聯結。 如需詳細資訊,請參閱 Transact-SQL) (資料表提示

<output_clause>

針對 target_table 中每個更新、插入或刪除的資料列傳回一個資料列 (不依特定順序)。 您可以在輸出子句中指定 $action$action 是一個 nvarchar(10) 類型的資料行,會為每個資料列傳回下列其中一個值:'INSERT'、'UPDATE' 或 'DELETE',根據在該資料列執行的動作而定。 OUTPUT 子句是查詢或計算 MERGE 所影響之資料列的建議方式。 如需這個子句之引數和行為的詳細資訊,請參閱 OUTPUT 子句 (Transact-SQL)

OPTION ( < query_hint > [ ,...n ] )

指定利用最佳化工具提示來自訂 Database Engine 處理陳述式的方式。 如需詳細資訊,請參閱 查詢提示 (Transact-SQL)

<merge_matched>

指定更新或刪除動作,這些動作會套用至不符合TABLE_SOURCE ON < merge_search_condition > 傳 <> 回之資料列的所有資料列target_table,並滿足任何其他搜尋條件。

UPDATE SET < set_clause>

指定要在目標資料表中更新的資料行或變數名稱清單,以及用於更新這些名稱的值。

如需這個子句引數的詳細資訊,請參閱 UPDATE (Transact-SQL) 。 不支援將變數設定為與資料行相同的值。

刪除

指定符合 target_table 資料列的資料列會遭到刪除。

<merge_not_matched>

指定要插入目標資料表的值。

(column_list)

要插入資料的一或多個目標資料表資料行清單。 必須將資料行指定為單一部分名稱,否則 MERGE 陳述式會失敗。 column_list 必須以括弧括住,並以逗號分隔。

VALUES ( values_list)

以逗號分隔的常數、變數或運算式清單,這些項目會傳回要插入至目標資料表的值。 運算式不能包含 EXECUTE 陳述式。

DEFAULT VALUES

強制插入的資料列包含定義給每個資料行的預設值。

如需這個子句的詳細資訊,請參閱 INSERT (Transact-SQL)

<search_condition>

指定要指定 < merge_search_condition > 或 < clause_search_condition > 的搜尋條件。 如需這個子句引數的詳細資訊,請參閱 Search Condition (Transact-SQL)

<圖形搜尋模式>

指定圖形搜尋模式。 如需這個子句引數的詳細資訊,請參閱 MATCH (Transact-SQL)

備註

注意

在 Azure Synapse Analytics 中,MERGE 命令與 SQL Server 和 Azure SQL 資料庫有下列差異。

  • 在比 之前的 10.0.17829.0 組建中不支援使用 MERGE 來更新散發索引鍵資料行。 如果無法暫停或強制升級,請使用 ANSI UPDATE FROM ... JOIN 語句作為因應措施,直到版本 10.0.17829.0 為止。
  • MERGE 更新是以 DELETE 和 INSERT 配對的形式實作。 MERGE 更新的受影響資料列計數會包含已刪除和已插入的資料列。
  • 合併。。。使用 IDENTITY 資料行的資料表不支援 NOT MATCHED INSERT 時。
  • 資料表值建構函式不能用於來源資料表的 USING 子句中。 使用 SELECT ... UNION ALL 建立具有多個資料列的衍生來源資料表。
  • 下表說明對具有不同散發類型之資料表的支援:
Azure Synapse Analytics 中的 MERGE 子句 支援的 TARGET 散發資料表 支援的 SOURCE 散發資料表 註解
WHEN MATCHED 所有散發類型 所有散發類型
NOT MATCHED BY TARGET HASH 所有散發類型 使用 UPDATE/DELETE FROM…JOIN 來同步兩個資料表。
NOT MATCHED BY SOURCE 所有散發類型 所有散發類型

提示

如果您使用散發雜湊鍵作為 MERGE 中的 JOIN 資料行,而且只執行相等比較,您可以從 子句中的資料 WHEN MATCHED THEN UPDATE SET 行清單中省略散發索引鍵,因為這是多餘的更新。

重要

在 Azure Synapse Analytics 中,建置的 MERGE 命令可能早 10.0.17829.0 于某些情況下,讓目標資料表處於不一致的狀態,且資料列置於錯誤的散發中,導致稍後的查詢在某些情況下傳回錯誤的結果。 此問題可能會在 2 種情況下發生:

案例 註解
案例 1
在包含次要索引或 UNIQUE 條件約束的 HASH 分散式 TARGET 資料表上使用 MERGE。
- 已修正 Synapse SQL 10.0.15563.0 版和更新版本。
- 如果傳 SELECT @@VERSION 回低於 10.0.15563.0 的版本,請手動暫停並繼續 Synapse SQL 集區以挑選此修正程式。
- 在修正套用至 Synapse SQL 集區之前,請避免在具有次要索引或 UNIQUE 條件約束的 HASH 分散式 TARGET 資料表上使用 MERGE 命令。
案例 2
使用 MERGE 更新 HASH 分散式資料表的散發索引鍵資料行。
- 已修正 Synapse SQL 10.0.17829.0 版和更新版本。
- 如果傳 SELECT @@VERSION 回低於 10.0.17829.0 的版本,請手動暫停並繼續 Synapse SQL 集區以挑選此修正程式。
- 在修正程式套用至 Synapse SQL 集區之前,請避免使用 MERGE 命令來更新散發索引鍵資料行。

請注意,這兩種案例中的更新都不會修復先前的 MERGE 執行所影響的資料表。 使用下列腳本來手動識別及修復任何受影響的資料表。

若要檢查資料庫中哪些雜湊分散式資料表在) 在案例中使用時,可能擔心 (,請執行此語句

-- Case 1
select a.name, c.distribution_policy_desc, b.type from sys.tables a join sys.indexes b
on a.object_id = b.object_id
join
sys.pdw_table_distribution_properties c
on a.object_id = c.object_id
where b.type = 2 and c.distribution_policy_desc = 'HASH';

-- Subject to Case 2, if distribution key value is updated in MERGE statement
select a.name, c.distribution_policy_desc from sys.tables a 
join
sys.pdw_table_distribution_properties c
on a.object_id = c.object_id
where c.distribution_policy_desc = 'HASH';

若要檢查 MERGE 的雜湊分散式資料表是否受到案例 1 或案例 2 的影響,請遵循下列步驟來檢查資料表是否有資料列落在錯誤的散發。 如果傳回「不需要修復」,此資料表不會受到影響。

if object_id('[check_table_1]', 'U') is not null
drop table [check_table_1]
go
if object_id('[check_table_2]', 'U') is not null
drop table [check_table_2]
go

create table [check_table_1] with(distribution = round_robin) as
select <DISTRIBUTION_COLUMN> as x from <MERGE_TABLE> group by <DISTRIBUTION_COLUMN>;
go

create table [check_table_2] with(distribution = hash(x)) as
select x from [check_table_1];
go

if not exists(select top 1 * from (select <DISTRIBUTION_COLUMN> as x from <MERGE_TABLE> except select x from 
[check_table_2]) as tmp)
select 'no need for repair' as result
else select 'needs repair' as result
go

if object_id('[check_table_1]', 'U') is not null
drop table [check_table_1]
go
if object_id('[check_table_2]', 'U') is not null
drop table [check_table_2]
go

若要修復受影響的資料表,請執行這些語句,將舊資料表中的所有資料列複製到新的資料表。

if object_id('[repair_table_temp]', 'U') is not null
drop table [repair_table_temp];
go
if object_id('[repair_table]', 'U') is not null
drop table [repair_table];
go
create table [repair_table_temp] with(distribution = round_robin) as select * from <MERGE_TABLE>;
go

-- [repair_table] will hold the repaired table generated from <MERGE_TABLE>
create table [repair_table] with(distribution = hash(<DISTRIBUTION_COLUMN>)) as select * from [repair_table_temp];
go
if object_id('[repair_table_temp]', 'U') is not null
drop table [repair_table_temp];
go

必須至少指定三個 MATCHED 子句中的一個,但可依任何順序指定這些子句。 在同一個 MATCHED 子句中,不能更新變數一次以上。

在目標資料表上由 MERGE 陳述式所指定的任何插入、更新或刪除動作,都受限於資料表上定義的任何條件約束,包括任何串聯式參考完整性條件約束。 如果在目標資料表上將任何唯一索引的 IGNORE_DUP_KEY 設定為 ON,則 MERGE 會忽略此設定。

MERGE 陳述式需要使用分號 (;) 做為陳述式結束字元。 若 MERGE 陳述式執行時缺少該結束字元,就會引發錯誤 10713。

在 MERGE 之後使用時, @@ROWCOUNT (Transact-SQL) 會傳回插入、更新和刪除至用戶端的資料列總數。

當資料庫相容性層級設定為 100 或更高時,MERGE 是完全保留的關鍵字。 雖然 MERGE 陳述式也可以在 90 和 100 資料庫相容性層級底下使用,但是當資料庫相容性層級設定為 90 時,此關鍵字並不會完全保留。

警告

使用 佇列更新複寫時,請勿使用 MERGE 語句。 MERGE 與佇列更新觸發程序不相容。 請將 MERGE 陳述式取代成 Insert 或 Update 陳述式。

疑難排解

在某些情況下,MERGE 語句可能會導致錯誤 “CREATE TABLE failed because column <> in table <> exceeds the maximum of 1024 columns.” ,即使 Target 和 Source 資料表都沒有 1024 個數據行也一定。 當符合下列所有條件時,可能會發生此案例:

  • MERGE 內的 UPDATE SET 或 INSERT 作業中指定多個資料行, (未指定于任何 WHEN [NOT] MATCHED 子句)
  • JOIN 條件中的任何資料行都有非叢集索引 (NCI)

如果發現這個錯誤,建議的因應措施是從 JOIN 資料行中移除非叢集索引 (NCI) ,或在沒有 NCI 的資料行上聯結。 如果您稍後更新基礎資料表以在 JOIN 資料行中包含 NCI,您的 MERGE 語句可能會在執行時間受到此錯誤的影響。 請參閱 DROP INDEX 以瞭解如何卸載非叢集索引。

觸發程式實作

對於 MERGE 語句中指定的每個插入、更新或刪除動作,SQL Server會引發目標資料表上定義的任何對應 AFTER 觸發程式,但不保證要先或最後引發觸發程式的動作。 為相同動作所定義的觸發程序會接受您指定的順序。 如需有關設定觸發程序引發順序的詳細資訊,請參閱指定第一個及最後一個觸發程序

如果針對 MERGE 陳述式所執行的插入、更新或刪除動作在目標資料表上定義啟用的 INSTEAD OF 觸發程序,則 MERGE 陳述式中指定的所有動作在目標資料表上都必須啟用 INSTEAD OF 觸發程序。

如果在 target_table 上定義了任何 INSTEAD OF UPDATE 或 INSTEAD OF DELETE 觸發程序,則不會執行更新或刪除作業。 會改為引發觸發程序,並據此填入 inserteddeleted 資料表。

如果在 target_table 上定義任何 INSTEAD OF INSERT 觸發程序,則不會執行插入作業。 反之,會據此填入資料表。

注意

不同于個別的 INSERT、UPDATE 和 DELETE 子句,觸發程式內部@@ROWCOUNT反映的資料列數目可能會較高。 不論觸發程式擷) 取的資料修改語句為何,任何 AFTER 觸發程式 (內的@@ROWCOUNT都會反映 MERGE 所影響的資料列總數。 例如,如果 MERGE 語句插入一個資料列、更新一個資料列,並刪除一個資料列,則即使只針對 INSERT 語句宣告觸發程式,@@ROWCOUNT也會是 3 個。

權限

來源資料表需要 SELECT 權限,目標資料表則需要 INSERT、UPDATE 或 DELETE 權限。 如需詳細資訊,請參閱 SELECTINSERTUPDATEDELETE 主題中的<權限>一節。

索引最佳做法

您可以藉由使用 MERGE 陳述式,以單一陳述式來取代個別 DML 陳述式。 這樣可以提升查詢效能,因為作業會在單一陳述式內執行,因此會讓來源和目標資料表中的資料處理次數減至最少。 但是,效能改善取決於是否有正確的索引、聯結以及適當進行其他考量。

若要改善 MERGE 陳述式的效能,我們建議您遵循索引指導方針:

  • 建立索引以加速 MERGE 來源與目標之間的聯結:
    • 在來源資料表的聯結資料行上建立索引,其索引鍵涵蓋目標資料表的聯結邏輯。 可能的話,它應該是唯一的。
    • 此外,在目標資料表的聯結資料行上建立索引。 可能的話,它應該是唯一的叢集索引。
    • 這兩個索引可確保資料表中的資料經過排序,而且唯一性有助於比較的效能。 查詢效能已改善,因為查詢最佳化工具不需要執行額外的驗證處理,才能找出並更新重複的資料列,而且不需要其他排序作業。
  • 避免使用任何形式的資料行存放區索引做為 MERGE 語句目標的資料表。 如同任何 UPDATEE,您可以藉由更新分段資料列存放區資料表,然後執行批次的 DELETE 和 INSERT,而不是 UPDATE 或 MERGE,來尋找資料行存放區索引的效能更好。

MERGE 的並行考慮

就鎖定而言,MERGE 與離散、連續 INSERT、UPDATE 和 DELETE 子句不同。 MERGE 仍會執行 INSERT、UPDATE 和 DELETE 作業,不過使用不同的鎖定機制。 針對某些應用程式需求,撰寫離散 INSERT、UPDATE 和 DELETE 子句可能會更有效率。 大規模地,MERGE 可能會引入複雜的並行問題,或需要進階疑難排解。 因此,規劃在部署到生產環境之前徹底測試任何 MERGE 語句。

MERGE 語句是 (中離散 INSERT、UPDATE 和 DELETE 作業的適當取代專案,但不限於下列案例) :

  • 涉及大型資料列計數的 ETL 作業會在非預期其他並行作業時執行。 預期的大量平行存取時,個別 INSERT、UPDATE 和 DELETE 邏輯的執行效能可能會比 MERGE 語句少一些。
  • 涉及小型資料列計數和交易的複雜作業不太可能在延長期間執行。
  • 涉及使用者資料表的複雜作業,其中索引的設計目的是要確保最佳執行計畫,避免使用索引掃描和查閱,或最好是索引搜尋。

並行的其他考慮:

  • 在某些預期 MERGE 同時插入和更新唯一索引鍵的案例中,指定 HOLDLOCK 將防止唯一索引鍵違規。 HOLDLOCK 是 SERIALIZABLE 交易隔離等級的同義字,不允許其他並行交易修改此交易已讀取的資料。 SERIALIZABLE 是最安全的隔離等級,但提供與其他交易的最少並行處理,以在讀取進行時防止插入或更新虛設資料列。 如需 HOLDLOCK 的詳細資訊,請參閱 提示SET TRANSACTION ISOLATION LEVEL (Transact-SQL)

JOIN 最佳做法

若要改善 MERGE 陳述式的效能,並確保可得到正確的結果,我們建議您遵循聯結指導方針:

  • 只在 ON < merge_search_condition > 子句中指定搜尋條件,以決定符合來源和目標資料表中資料的準則。 也就是說,只從目標資料表中指定要與來源資料表的對應資料行進行比較的資料行。
  • 請勿包含與其他值的比較,例如常數。

若要從來源或目標資料表中篩選掉資料列,請使用下列其中一個方法。

  • 在適當的 WHEN 子句中指定資料列篩選的搜尋條件。 例如, WHEN NOT MATCHED AND S.EmployeeName LIKE 'S%' THEN INSERT....
  • 在來源或目標上定義一個可傳回篩選過之資料列的檢視表,並將此檢視表當做來源或目標資料表來參考。 如果此檢視表定義在目標資料表上,則對其進行的任何動作都必須滿足更新檢視表的條件。 如需有關使用檢視表來更新資料的詳細資訊,請參閱<透過檢視修改資料>。
  • 使用 WITH <common table expression> 子句,從來源或目標資料表中篩選掉資料列。 這個方法類似於在 ON 子句中指定其他搜尋條件,而且可能會產生不正確的結果。 我們建議您最好避免使用這個方法,或是在實作這個方法之前先徹底加以測試。

MERGE 陳述式中的聯結作業會使用與 SELECT 陳述式中的聯結相同的方式來最佳化。 也就是說,當SQL Server處理聯結時,查詢最佳化工具會選擇最有效率的方法 (處理聯結) 。 當來源和目標的大小類似,而且先前所述的索引指導方針適用於來源和目標資料表時,合併聯結運算子就是最有效率的查詢計畫。 這是因為這兩個數據表都會掃描一次,而且不需要排序資料。 當來源小於目標資料表時,偏好的是巢狀迴圈運算子。

您可以在 MERGE 陳述式中指定 OPTION (<query_hint>) 子句來強制使用特定聯結。 建議您不要使用雜湊聯結作為 MERGE 語句的查詢提示,因為此聯結類型不會使用索引。

參數化最佳做法

如果執行 SELECT、INSERT、UPDATE 或 DELETE 陳述式時不含參數,SQL Server 查詢最佳化工具可選擇在內部參數化該陳述式。 這表示會以參數替代包含在查詢中的任何常值。 例如,語句 INSERT dbo.MyTable (Col1, Col2) VALUES (1, 10) 可以在內部實作為 INSERT dbo.MyTable (Col1, Col2) VALUES (@p1, @p2) 。 此程式稱為簡單參數化,可增加關聯式引擎將新的 SQL 語句與先前編譯的執行計畫相符的能力。 查詢效能可能會提升,因為查詢編譯與重新編譯的頻率會減少。 查詢最佳化工具不會將簡單的參數化程式套用至 MERGE 語句。 因此,包含常值的 MERGE 語句可能不會執行和個別 INSERT、UPDATE 或 DELETE 子句,因為每次執行 MERGE 語句時都會編譯新的計畫。

若要改善查詢效能,我們建議您遵循參數化指導方針:

  • 參數化 子句和 MERGE 語句子句中的所有 WHEN 常值 ON <merge_search_condition> 。 例如,您可以將 MERGE 陳述式併入預存程序中,以適當的輸入參數取代常值。
  • 如果您無法參數化語句,請建立類型的 TEMPLATE 計劃指南,並在計劃指南中指定 PARAMETERIZATION FORCED 查詢提示。 如需詳細資訊,請參閱使用計劃指南指定查詢參數化行為
  • 如果經常在資料庫中執行 MERGE 陳述式,請考慮在資料庫上將 PARAMETERIZATION 選項設定為 FORCED。 當設定這個選項時,請務必小心。 PARAMETERIZATION 選項是資料庫層級設定,而且會影響對資料庫進行之所有查詢的處理方式。 如需詳細資訊,請參閱 強制參數化
  • 作為較新且更容易規劃指南的替代方案,請考慮具有查詢存放區提示的類似策略。 如需詳細資訊,請參閱查詢存放區提示

TOP 子句最佳做法

在 MERGE 語句中,TOP 子句會指定在來源資料表和目標資料表聯結之後受到影響的資料列數目或百分比,以及移除不符合插入、更新或刪除動作資格的資料列之後。 TOP 子句會進一步將聯結的資料列數減少為指定的值,而且插入、更新或刪除動作會依照未排序的方式套用到剩餘的聯結資料列。 亦即,將資料列散發到 WHEN 子句中定義的動作時,沒有任何特定順序。 例如,指定 TOP (10) 會影響 10 個資料列;在這些資料列中,可能會更新 7 個及插入 3 個,或者可能會刪除 1 個、更新 5 個及插入 4 個,依此類推。

通常會使用 TOP 子句,以批次方式在大型資料表上執行資料操作語言 (DML) 作業。 針對此目的在 MERGE 語句中使用 TOP 子句時,請務必瞭解下列含意。

  • I/O 效能可能會受到影響。

    MERGE 陳述式會針對來源和目標資料表執行完整資料表掃描。 將作業分成若干批次時,將會減少每一個批次執行的寫入作業數;但是,每一個批次都將執行來源和目標資料表的完整資料表掃描。 產生的讀取活動可能會影響查詢和其他資料表上並行活動的效能。

  • 可能產生不正確的結果。

    請務必確定所有後續批次都會以新的資料列或不想要的行為為目標,例如不正確地將重複的資料列插入目標資料表中。 當來源資料表包含的資料列不在目標批次中,但位於整體目標資料表時,就會發生這種情況。 若要確保正確結果:

    • 使用 ON 子句來判斷哪些來源資料列會影響現有的全新目標資料列。
    • 在 WHEN MATCHED 子句中使用其他條件來判斷上一個批次是否已經更新目標資料列。
    • 使用 WHEN MATCHED 子句和 SET 邏輯中的其他條件,確認無法更新相同的資料列兩次。

因為只有在套用這些子句之後才會套用 TOP 子句,所以每一次的執行會插入一個完全不相符的資料列,或是更新一個現有的資料列。

大量載入最佳做法

使用 MERGE 陳述式可有效率地從來源資料檔將資料大量載入目標資料表中,其方式是將 OPENROWSET(BULK…) 子句指定為資料表來源。 這樣做的話,整個檔案都會在單一批次中處理。

若要改善大量合併程序的效能,我們建議您遵循下列指導方針:

  • 在目標資料表的聯結資料行上,建立叢集索引。

  • 在大量載入 MERGE 期間停用目標資料表上其他非唯一的非叢集索引,之後再加以啟用。 這對於夜間大量資料作業很常見且實用。

  • OPENROWSET(BULK…) 子句中使用 ORDER 和 UNIQUE 提示來指定來源資料檔的排序方式。

    依預設,大量作業會假設資料檔沒有排序。 因此,請務必根據目標資料表上的叢集索引來排序來源資料,並使用 ORDER 提示來指出順序,讓查詢最佳化工具可以產生更有效率的查詢計劃。 提示會在執行時間進行驗證;如果資料流程不符合指定的提示,就會引發錯誤。

這些指導方針可確保聯結索引鍵是唯一的,而且來源檔案中的資料排序次序會符合目標資料表。 查詢效能已改善,因為不需要額外的排序作業,而且不需要不必要的資料複製。

測量和診斷 MERGE 效能

下列功能可協助您衡量及診斷 MERGE 陳述式的效能。

範例

A. 以單一陳述式使用 MERGE 在資料表上執行 INSERT 和 UPDATE 作業

如果存在相符的資料列,則常見情況是更新資料表中的一或多個資料行。 另一個情況是,如果沒有相符的資料列,則將資料作為新資料列插入。 您通常會透過將參數傳遞到包含適當 UPDATE 和 INSERT 陳述式的預存程序,依照上述情況之一來執行。 您可以使用 MERGE 陳述式,在單一陳述式中同時執行兩個工作。 下列範例顯示 AdventureWorks2019 資料庫中包含 INSERT 語句和 UPDATE 語句的預存程式。 接著,程序會經過修改,以便使用單一 MERGE 陳述式來執行等同的作業。

CREATE PROCEDURE dbo.InsertUnitMeasure  
    @UnitMeasureCode nchar(3),  
    @Name nvarchar(25)  
AS
BEGIN  
    SET NOCOUNT ON;  
-- Update the row if it exists.
    UPDATE Production.UnitMeasure  
SET Name = @Name  
WHERE UnitMeasureCode = @UnitMeasureCode  
-- Insert the row if the UPDATE statement failed.  
IF (@@ROWCOUNT = 0 )  
BEGIN  
    INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name)  
    VALUES (@UnitMeasureCode, @Name)  
END  
END;  
GO  
-- Test the procedure and return the results.  
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Test Value';  
SELECT UnitMeasureCode, Name FROM Production.UnitMeasure  
WHERE UnitMeasureCode = 'ABC';  
GO  

-- Rewrite the procedure to perform the same operations using the
-- MERGE statement.  
-- Create a temporary table to hold the updated or inserted values
-- from the OUTPUT clause.  
CREATE TABLE #MyTempTable  
    (ExistingCode nchar(3),  
     ExistingName nvarchar(50),  
     ExistingDate datetime,  
     ActionTaken nvarchar(10),  
     NewCode nchar(3),  
     NewName nvarchar(50),  
     NewDate datetime  
    );  
GO  
ALTER PROCEDURE dbo.InsertUnitMeasure  
    @UnitMeasureCode nchar(3),  
    @Name nvarchar(25)  
AS
BEGIN  
    SET NOCOUNT ON;  

    MERGE Production.UnitMeasure AS tgt  
    USING (SELECT @UnitMeasureCode, @Name) as src (UnitMeasureCode, Name)  
    ON (tgt.UnitMeasureCode = src.UnitMeasureCode)  
    WHEN MATCHED THEN
        UPDATE SET Name = src.Name  
    WHEN NOT MATCHED THEN  
        INSERT (UnitMeasureCode, Name)  
        VALUES (src.UnitMeasureCode, src.Name)  
    OUTPUT deleted.*, $action, inserted.* INTO #MyTempTable;  
END;  
GO  
-- Test the procedure and return the results.  
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'New Test Value';  
EXEC InsertUnitMeasure @UnitMeasureCode = 'XYZ', @Name = 'Test Value';  
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Another Test Value';  

SELECT * FROM #MyTempTable;  
-- Cleanup
DELETE FROM Production.UnitMeasure WHERE UnitMeasureCode IN ('ABC','XYZ');  
DROP TABLE #MyTempTable;  
GO  
CREATE PROCEDURE dbo.InsertUnitMeasure  
    @UnitMeasureCode nchar(3),  
    @Name nvarchar(25)  
AS
BEGIN  
    SET NOCOUNT ON;  
-- Update the row if it exists.
    UPDATE Production.UnitMeasure  
SET Name = @Name  
WHERE UnitMeasureCode = @UnitMeasureCode  
-- Insert the row if the UPDATE statement failed.  
IF (@@ROWCOUNT = 0 )  
BEGIN  
    INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name)  
    VALUES (@UnitMeasureCode, @Name)  
END  
END;  
GO  
-- Test the procedure and return the results.  
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Test Value';  
SELECT UnitMeasureCode, Name FROM Production.UnitMeasure  
WHERE UnitMeasureCode = 'ABC';  
GO  

-- Rewrite the procedure to perform the same operations using the
-- MERGE statement.
ALTER PROCEDURE dbo.InsertUnitMeasure  
    @UnitMeasureCode nchar(3),  
    @Name nvarchar(25)  
AS
BEGIN  
    SET NOCOUNT ON;  

    MERGE Production.UnitMeasure AS tgt  
    USING (SELECT @UnitMeasureCode, @Name) as src (UnitMeasureCode, Name)  
    ON (tgt.UnitMeasureCode = src.UnitMeasureCode)  
    WHEN MATCHED THEN
        UPDATE SET Name = src.Name  
    WHEN NOT MATCHED THEN  
        INSERT (UnitMeasureCode, Name)  
        VALUES (src.UnitMeasureCode, src.Name);  
END;  
GO  
-- Test the procedure and return the results.  
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'New Test Value';  
EXEC InsertUnitMeasure @UnitMeasureCode = 'XYZ', @Name = 'Test Value';  
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Another Test Value';  

-- Cleanup
DELETE FROM Production.UnitMeasure WHERE UnitMeasureCode IN ('ABC','XYZ');  
GO  

B. 以單一陳述式使用 MERGE 在資料表上執行 UPDATE 和 DELETE 作業

下列範例會根據資料表中處理的順序,使用 MERGE 來更新 ProductInventory AdventureWorks2019 範例資料庫中的 SalesOrderDetail 資料表。 Quantity 資料表中的 ProductInventory 資料行會藉著減去 SalesOrderDetail 資料表中每個產品每日所下的訂單數量來進行更新。 如果產品的訂單數量使產品的存貨降為 0 或 0 以下,該產品的資料列就會從 ProductInventory 資料表中刪除。

CREATE PROCEDURE Production.usp_UpdateInventory  
    @OrderDate datetime  
AS  
MERGE Production.ProductInventory AS tgt  
USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod  
    JOIN Sales.SalesOrderHeader AS soh  
    ON sod.SalesOrderID = soh.SalesOrderID  
    AND soh.OrderDate = @OrderDate  
    GROUP BY ProductID) as src (ProductID, OrderQty)  
ON (tgt.ProductID = src.ProductID)  
WHEN MATCHED AND tgt.Quantity - src.OrderQty <= 0  
    THEN DELETE  
WHEN MATCHED
    THEN UPDATE SET tgt.Quantity = tgt.Quantity - src.OrderQty,
                    tgt.ModifiedDate = GETDATE()  
OUTPUT $action, Inserted.ProductID, Inserted.Quantity,
    Inserted.ModifiedDate, Deleted.ProductID,  
    Deleted.Quantity, Deleted.ModifiedDate;  
GO  

EXECUTE Production.usp_UpdateInventory '20030501';  
CREATE PROCEDURE Production.usp_UpdateInventory  
    @OrderDate datetime  
AS  
MERGE Production.ProductInventory AS tgt  
USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod  
    JOIN Sales.SalesOrderHeader AS soh  
    ON sod.SalesOrderID = soh.SalesOrderID  
    AND soh.OrderDate = @OrderDate  
    GROUP BY ProductID) as src (ProductID, OrderQty)  
ON (tgt.ProductID = src.ProductID)  
WHEN MATCHED AND tgt.Quantity - src.OrderQty <= 0  
    THEN DELETE  
WHEN MATCHED
    THEN UPDATE SET tgt.Quantity = tgt.Quantity - src.OrderQty,
                    tgt.ModifiedDate = GETDATE();  
GO  

EXECUTE Production.usp_UpdateInventory '20030501';  

C. 使用 MERGE 在目標資料表上透過衍生的來源資料表來執行 UPDATE 和 INSERT 作業

下列範例會藉由更新或插入資料列,使用 MERGE 修改 SalesReason AdventureWorks2019 資料庫中的資料表。

當來源資料表中的 NewName 值符合目標資料表 (Name) 中 SalesReason 資料行內的值時,就會更新目標資料表中的 ReasonType 資料行。 當 NewName 的值不相符時,來源資料列會插入目標資料表中。 來源資料表是衍生資料表,使用 Transact-SQL 資料表值建構函式來指定來源資料表的多個資料列。 如需在衍生資料表中使用資料表值建構函式的詳細資訊,請參閱 資料表值建構函式 (Transact-SQL)

OUTPUT 子句可用於查詢 MERGE 語句的結果,如需詳細資訊,請參閱 OUTPUT 子句。 此範例也示範如何將 OUTPUT 子句的結果儲存在資料表變數中。 接著,您執行簡單的選取作業來摘要 MERGE 陳述式的結果,該作業會傳回插入和更新的資料列數。

-- Create a temporary table variable to hold the output actions.  
DECLARE @SummaryOfChanges TABLE(Change VARCHAR(20));  

MERGE INTO Sales.SalesReason AS tgt  
USING (VALUES ('Recommendation','Other'), ('Review', 'Marketing'),
              ('Internet', 'Promotion'))  
       as src (NewName, NewReasonType)  
ON tgt.Name = src.NewName  
WHEN MATCHED THEN  
UPDATE SET ReasonType = src.NewReasonType  
WHEN NOT MATCHED BY TARGET THEN  
INSERT (Name, ReasonType) VALUES (NewName, NewReasonType)  
OUTPUT $action INTO @SummaryOfChanges;  

-- Query the results of the table variable.  
SELECT Change, COUNT(*) AS CountPerChange  
FROM @SummaryOfChanges  
GROUP BY Change;  

當來源資料表中的 NewName 值符合目標資料表 (Name) 中 SalesReason 資料行內的值時,就會更新目標資料表中的 ReasonType 資料行。 當 NewName 的值不相符時,來源資料列會插入目標資料表中。 來源資料表是衍生資料表,用來 SELECT ... UNION ALL 指定來源資料表的多個資料列。

MERGE INTO Sales.SalesReason AS tgt  
USING (SELECT 'Recommendation','Other' UNION ALL 
       SELECT 'Review', 'Marketing' UNION ALL 
       SELECT 'Internet', 'Promotion')
   as src (NewName, NewReasonType)  
ON tgt.Name = src.NewName  
WHEN MATCHED THEN  
UPDATE SET ReasonType = src.NewReasonType  
WHEN NOT MATCHED BY TARGET THEN  
INSERT (Name, ReasonType) VALUES (NewName, NewReasonType);  

D. 將 MERGE 陳述式的結果插入另一個資料表

下列範例將擷取從 MERGE 陳述式的 OUTPUT 子句中傳回的資料,並將該資料插入另一個資料表中。 MERGE 語句會根據資料表中處理的順序,更新 QuantityProductInventory AdventureWorks2019 資料庫中資料表的資料 SalesOrderDetail 行。 此範例會擷取更新的資料列,並將其插入至另一個資料表,該資料表用於追蹤存貨變更。

CREATE TABLE Production.UpdatedInventory  
    (ProductID INT NOT NULL, LocationID int, NewQty int, PreviousQty int,  
     CONSTRAINT PK_Inventory PRIMARY KEY CLUSTERED (ProductID, LocationID));  
GO  
INSERT INTO Production.UpdatedInventory  
SELECT ProductID, LocationID, NewQty, PreviousQty
FROM  
(    MERGE Production.ProductInventory AS pi  
     USING (SELECT ProductID, SUM(OrderQty)
            FROM Sales.SalesOrderDetail AS sod  
            JOIN Sales.SalesOrderHeader AS soh  
            ON sod.SalesOrderID = soh.SalesOrderID  
            AND soh.OrderDate BETWEEN '20030701' AND '20030731'  
            GROUP BY ProductID) AS src (ProductID, OrderQty)  
     ON pi.ProductID = src.ProductID  
    WHEN MATCHED AND pi.Quantity - src.OrderQty >= 0
        THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty  
    WHEN MATCHED AND pi.Quantity - src.OrderQty <= 0
        THEN DELETE  
    OUTPUT $action, Inserted.ProductID, Inserted.LocationID,
        Inserted.Quantity AS NewQty, Deleted.Quantity AS PreviousQty)  
 AS Changes (Action, ProductID, LocationID, NewQty, PreviousQty)
 WHERE Action = 'UPDATE';  
GO  

E. 使用 MERGE 對圖形資料庫中的目標邊緣資料表執行 INSERT 或 UPDATE

在此範例中,您會建立 PersonCity 節點資料表以及 livesIn 邊緣資料表。 您將在 livesIn 邊緣使用 MERGE 陳述式,以在 PersonCity 之間還未存在邊緣時插入新資料列。 如果邊緣已經存在,則您只需更新 livesIn 邊緣上的 StreetAddress 屬性。

-- CREATE node and edge tables
CREATE TABLE Person
    (
        ID INTEGER PRIMARY KEY,
        PersonName VARCHAR(100)
    )
AS NODE
GO

CREATE TABLE City
    (
        ID INTEGER PRIMARY KEY,
        CityName VARCHAR(100),
        StateName VARCHAR(100)
    )
AS NODE
GO

CREATE TABLE livesIn
    (
        StreetAddress VARCHAR(100)
    )
AS EDGE
GO

-- INSERT some test data into node and edge tables
INSERT INTO Person VALUES (1, 'Ron'), (2, 'David'), (3, 'Nancy')
GO

INSERT INTO City VALUES (1, 'Redmond', 'Washington'), (2, 'Seattle', 'Washington')
GO

INSERT livesIn SELECT P.$node_id, C.$node_id, c
FROM Person P, City C, (values (1,1, '123 Avenue'), (2,2,'Main Street')) v(a,b,c)
WHERE P.id = a AND C.id = b
GO

-- Use MERGE to update/insert edge data
CREATE OR ALTER PROCEDURE mergeEdge
    @PersonId integer,
    @CityId integer,
    @StreetAddress varchar(100)
AS
BEGIN
    MERGE livesIn
        USING ((SELECT @PersonId, @CityId, @StreetAddress) AS T (PersonId, CityId, StreetAddress)
                JOIN Person ON T.PersonId = Person.ID
                JOIN City ON T.CityId = City.ID)
        ON MATCH (Person-(livesIn)->City)
    WHEN MATCHED THEN
        UPDATE SET StreetAddress = @StreetAddress
    WHEN NOT MATCHED THEN
        INSERT ($from_id, $to_id, StreetAddress)
        VALUES (Person.$node_id, City.$node_id, @StreetAddress) ;
END
GO

-- Following will insert a new edge in the livesIn edge table
EXEC mergeEdge 3, 2, '4444th Avenue'
GO

-- Following will update the StreetAddress on the edge that connects Ron to Redmond
EXEC mergeEdge 1, 1, '321 Avenue'
GO

-- Verify that all the address were added/updated correctly
SELECT PersonName, CityName, StreetAddress
FROM Person , City , livesIn
WHERE MATCH(Person-(livesIn)->city)
GO

另請參閱