1. In any performance testing discussion for large development one of the common requirements has been to send a multiple values to SQL Server in one shot. So to help this requirement, SQL Server introduced the MERGE statement.

Yes, this a very useful technique introduced in SQL Server 2008. A special case is passing multiple values in a table valued parameter to the proc and doing the transaction like insert/update and delete at one shot with respect to the source table values.Excellent tool for ETL.

2. What is the construct of a MERGE statement?

MERGE is a new feature that provides an efficient way to perform multiple DML operations. In previous versions of SQL Server, we had to write separate statements to INSERT, UPDATE, or DELETE data based on certain conditions, but now, using MERGE statement we can include the logic of such data modifications in one statement that even checks when the data is matched then just update it and when unmatched then insert it.

One of the most important advantage of MERGE statement is all the data is read and processed only once. In previous versions three different statement has to be written to process three different activity (INSERT, UPDATE or DELETE), however using MERGE statement all update activity can be done in one pass of database table. This is quite an improvement in performance of database query.

MERGE [INTO] 
USING 
ON  (semantics similar to outer join)
WHEN MATCHED 
WHEN [TARGET] NOT MATCHED 

Eg:

CREATE TABLE dbo.tbl_Source (id INT, name NVARCHAR(100), qty INT);
CREATE TABLE dbo.tbl_Target (id INT, name NVARCHAR(100), qty INT);

--Synchronize source data with target
MERGE INTO dbo.tbl_Target AS t
    USING dbo.tbl_Source AS s    
        ON t.id = s.id
    WHEN MATCHED AND (t.name != s.name OR t.qty!= s.qty) THEN
        --Row exists and data is different
        UPDATE SET t.name = s.name, t.qty = s.qty
    WHEN NOT MATCHED THEN 
        --Row exists in source but not in target
        INSERT VALUES (s.id, s.name, s.qty) 
    WHEN SOURCE NOT MATCHED THEN 
        --Row exists in target but not in source
        DELETE OUTPUT$action, inserted.id, deleted.id

3. What are the restrictions of using the same?

1. The ORDER BY in the merge wont work unless you have a TOP Clause.
2. SQL Server doesn’t allow a MERGE statement to modify the same row more than once. A MERGE statement will fail if it tries to update the same row more than once, or update and delete the same row.
3. A MERGE statement may not enforce a foreign key constraint when the statement updates a unique key column that is not part of a clustering key and there is a single row as the update source in SQL Server 2008.(This issue has been fixed in the cumulative update(KB Article:956717)
4. Indexes may not be used if the MERGE statement has provided with HASH JOIN query hint.
5. The query optimizer does not apply the simple parameterization process to MERGE statements. Therefore, MERGE statements that contain literal values may not perform as well as individual INSERT, UPDATE, or DELETE statements because a new plan is compiled each time the MERGE statement is executed.

SQL SERVER 2008 Introduction to Merge Statement – One Statement for INSERT, UPDATE, DELETE

MERGE is a new feature that provides an efficient way to perform multiple DML operations. In previous versions of SQL Server, we had to write separate statements to INSERT, UPDATE, or DELETE data based on certain conditions, but now, using MERGE statement we can include the logic of such data modifications in one statement that even checks when the data is matched then just update it and when unmatched then insert it.

One of the most important advantage of MERGE statement is all the data is read and processed only once. In previous versions three different statement has to be written to process three different activity (INSERT, UPDATE or DELETE), however using MERGE statement all update activity can be done in one pass of database table. This is quite an improvement in performance of database query.

Syntax of MERGE statement is as following:

MERGE
[ TOP ( expression ) [ PERCENT ] ]
[ INTO ] target_table [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias]
USING <table_source>
ON <merge_search_condition>
[ WHEN MATCHED [ AND <clause_search_condition> ]
THEN <merge_matched> ]
[ 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> ]
[ <output_clause> ]
[ OPTION ( <query_hint> [ ,...n ] ) ]
;

Example:

Let’s create Student Details and StudentTotalMarks and inserted some records.

Student Details:

USE AdventureWorks
GO
CREATE TABLE StudentDetails
(
StudentID INTEGER PRIMARY KEY,
StudentName VARCHAR(15)
)
GO
INSERT INTO StudentDetails
VALUES(1,'SMITH')
INSERT INTO StudentDetails
VALUES(2,'ALLEN')
INSERT INTO StudentDetails
VALUES(3,'JONES')
INSERT INTO StudentDetails
VALUES(4,'MARTIN')
INSERT INTO StudentDetails
VALUES(5,'JAMES')
GO

StudentTotalMarks:

CREATE TABLE StudentTotalMarks
(
StudentID INTEGER REFERENCES StudentDetails,
StudentMarks INTEGER
)
GO
INSERT INTO StudentTotalMarks
VALUES(1,230)
INSERT INTO StudentTotalMarks
VALUES(2,255)
INSERT INTO StudentTotalMarks
VALUES(3,200)
GO

In our example we will consider three main conditions while we merge this two tables.

Delete the records whose marks are more than 250. Update marks and add 25 to each as internals if records exist. Insert the records if record does not exists. Now we will write MERGE process for tables created earlier. We will make sure that we will have our three conditions discussed above are satisfied.

MERGE StudentTotalMarks AS stm
USING (SELECT StudentID,StudentName FROM StudentDetails) AS sd
ON stm.StudentID = sd.StudentID
WHEN MATCHED AND stm.StudentMarks > 250 THEN DELETE
WHEN MATCHED THEN UPDATE SET stm.StudentMarks = stm.StudentMarks + 25
WHEN NOT MATCHED THEN
INSERT(StudentID,StudentMarks)
VALUES(sd.StudentID,25);
GO

There are two very important points to remember while using MERGE statement.

• Semicolon is mandatory after the merge statement.
• When there is a MATCH clause used along with some condition, it has to be specified first amongst all other WHEN MATCH clause.

AS we can see there are 5 rows updated. StudentID 2 is deleted as it is more than 250, 25 marks have been added to all records that exists i.e StudentID 1,3 and the records that did not exists i.e. 4 and 5 are now inserted in StudentTotalMarks .

MERGE statement is very handy improvement for T-SQL developers who have to update database tables with complicated logic. MERGE statement also improves the performance of database as it passes through data only once.

Restrictions

• At least one of the three MATCHED clauses must be specified, but they can be specified in any order. A variable cannot be updated more than once in the same MATCHED clause.

• Any insert, update, or delete actions specified on the target table by the MERGE statement are limited by any constraints defined on it, including any cascading referential integrity constraints. If IGNOREDUPKEY is set to ON for any unique indexes on the target table, MERGE ignores this setting.

• The MERGE statement requires a semicolon (;) as a statement terminator. Error 10713 is raised when a MERGE statement is run without the terminator.

• When used after MERGE, @@ROWCOUNT (Transact-SQL) returns the total number of rows inserted, updated, and deleted to the client.

• MERGE is a fully reserved keyword when the database compatibility level is set to 100. The MERGE statement is available under both 90 and 100 database compatibility levels; however the keyword is not fully reserved when the database compatibility level is set to 90.

• After the MERGE statement has been executed, we should compare previous resultset and new resultset to verify if our three conditions are carried out.

• SQL Server doesn’t allow a MERGE statement to modify the same row more than once. A MERGE statement will fail if it tries to update the same row more than once, or update and delete the same row.

• Requires SELECT permission on the source table and INSERT, UPDATE, or DELETE permissions on the target table.

• A MERGE statement may not enforce a foreign key constraint when the statement updates a unique key column that is not part of a clustering key and there is a single row as the update source in SQL Server 2008.Microsoft has created the first Hotfix for SQL Server 2008 which will fix this issue. http://support.microsoft.com/kb/956718

Trigger Implementation

For every insert, update, or delete action specified in the MERGE statement, SQL Server fires any corresponding AFTER triggers defined on the target table, but does not guarantee on which action to fire triggers first or last. Triggers defined for the same action honor the order you specify. For more information about setting trigger firing order, see Specifying First and Last Triggers.

If the target table has an enabled INSTEAD OF trigger defined on it for an insert, update, or delete action performed by a MERGE statement, then it must have an enabled INSTEAD OF trigger for all of the actions specified in the MERGE statement.

If there are any INSTEAD OF UPDATE or INSTEAD OF DELETE triggers defined on target_table, the update or delete operations are not performed. Instead, the triggers fire and the inserted and deleted tables are populated accordingly.

If there are any INSTEAD OF INSERT triggers defined on target_table, the insert operation is not performed. Instead, the triggers fire and the inserted table is populated accordingly.

http://blog.sqlauthority.com/2008/08/28/sql-server-2008-introduction-to-merge-statement-one-statement-for-insert-update-delete

http://technet.microsoft.com/en-us/library/bb510625.aspx

http://www.sqlmag.com/article/tsql3/beyond-merge-s-basics

MERGE is a new feature that provides an efficient way to perform multiple DML operations. In previous versions of SQL Server, we had to write separate statements to INSERT, UPDATE, or DELETE data based on certain conditions, but now, using MERGE statement we can include the logic of such data modifications in one statement that even checks when the data is matched then just update it and when unmatched then insert it. One of the most important advantages of MERGE statement is all the data is read and processed only once.

How MERGE works

The MERGE statement basically works as separate insert, update, and delete statements all within the same statement. You specify a “Source” record set and a “Target” table, and the join between the two. You then specify the type of data modification that is to occur when the records between the two data are matched or are not matched. MERGE is very useful, especially when it comes to loading data warehouse tables, which can be very large and require specific actions to be taken when rows are or are not present.

One of main part of Merge is Table Value Constructor (Transact-SQL) Specifies a set of row value expressions to be constructed into a table. The Transact-SQL table value constructor allows multiple rows of data to be specified in a single DML statement.

The table value constructor can be specified in the VALUES clause of the INSERT statement, in the USING clause of the MERGE statement, and in the definition of a derived table in the FROM clause.

Syntax of Merge Please Click:: http://msdn.microsoft.com/en-us/library/bb510625.aspx

MERGE
[ TOP ( expression ) [ PERCENT ] ]
[ INTO ] target_table [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias]
USING <table_source>
ON <merge_search_condition>
[ WHEN MATCHED [ AND <clause_search_condition> ]
THEN <merge_matched> ]
[ 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> ]
[ <output_clause> ]
[ OPTION ( <query_hint> [ ,...n ] ) ]
;

There are two very important points to remember while using MERGE statement.

1) Semicolon is mandatory after the merge statement.

2) When there is a MATCH clause used along with some condition, it has to be specified first amongst all other WHEN MATCH clause.

Limitations and Restrictions

• Merge join itself is very fast, but it can be an expensive choice if sort operations are required.

• At least one of the three MATCHED clauses must be specified, but they can be specified in any order. A variable cannot be updated more than once in the same MATCHED clause.

• Any insert, update, or delete actions specified on the target table by the MERGE statement are limited by any constraints defined on it, including any cascading referential integrity constraints. If IGNOREDUPKEY is set to ON for any unique indexes on the target table, MERGE ignores this setting.

• The MERGE statement requires a semicolon (;) as a statement terminator. Error 10713 is raised when a MERGE statement is run without the terminator.

• When used after MERGE, @@ROWCOUNT (Transact-SQL) returns the total number of rows inserted, updated, and deleted to the client. not giving separate count of individual operation.

• MERGE is a fully reserved keyword when the database compatibility level is set to 100. The MERGE statement is available under both 90 and 100 database compatibility levels; however the keyword is not fully reserved when the database compatibility level is set to 90.

• For every insert, update, or delete action specified in the MERGE statement, SQL Server fires any corresponding AFTER triggers defined on the target table, but does not guarantee on which action to fire triggers first or last.

• In SQl 2012, The maximum number of rows that can be constructed using the table value constructor is 1000. To insert more than 1000 rows, create multiple INSERT statements, or bulk import the data by using the bcp utility or the BULK INSERT statement.

• Only single scalar values are allowed as a row value expression (Plese See Table Value Constructor). A subquery that involves multiple columns is not allowed as a row value expression. For example, the following code results in a syntax error because the third row value expression list contains a subquery with multiple columns.

• If the target table has an enabled INSTEAD OF trigger defined on it for an insert, update, or delete action performed by a MERGE statement, then it must have an enabled INSTEAD OF trigger for all of the actions specified in the MERGE statement.

• If there are any INSTEAD OF UPDATE or INSTEAD OF DELETE triggers defined on target_table, the update or delete operations are not performed. Instead, the triggers fire and the inserted and deleted tables are populated accordingly.

• If there are any INSTEAD OF INSERT triggers defined on target_table, the insert operation is not performed. Instead, the triggers fire and the inserted table is populated accordingly.

• Requires SELECT permission on the source table and INSERT, UPDATE, or DELETE permissions on the target table.

Best example to understant Insert, Update and Delete using merge

-- Example 1 (Delete,Update,Insert Using Merge)
-- Create #Stock and #Trades tables
    CREATE TABLE #Stock(Stock varchar(10) NOT NULL, Qty int NOT NULL,) ON [PRIMARY];
    ALTER TABLE #Stock WITH CHECK ADD  CONSTRAINT [CK_Stock] CHECK  (([Qty]>(0)));

    CREATE TABLE #Trades(Stock varchar(10) NOT NULL, Delta int NOT NULL,) ON [PRIMARY];
    ALTER TABLE #Trades WITH CHECK ADD  CONSTRAINT [CK_Delta] CHECK  (([Delta]<>(0)));

-- Add some data to the tables
    INSERT #Stock VALUES('MSFT', 10), ('BOEING', 5);
    INSERT #Trades VALUES('MSFT', 5), ('BOEING', -5), ('GE', 3);

-- Check data in both tables
    SELECT * FROM #Stock
    SELECT * FROM #Trades

-- Merge the data
    MERGE #Stock S
    USING #Trades T
    ON S.Stock = T.Stock
    WHEN MATCHED AND (Qty + Delta = 0) THEN
        DELETE -- delete #Stock if entirely sold
    WHEN MATCHED THEN
        -- delete takes precedence over update
        UPDATE SET Qty += Delta
    WHEN NOT MATCHED THEN
        INSERT VALUES (Stock, Delta);
        -- BOEING is deleted, GE inserted, MSFT updated

-- Check data in #Stock tables
    SELECT * FROM #Stock

--Drop Temp Table 
    Drop table #Stock
    Drop table #Trades

MSQL Merge http://msdn.microsoft.com/en-us/library/bb510625(v=sql.110).aspx

http://forum.kimballgroup.com/t885-ssis-and-the-sql-2008-merge-statement-for-insert-update-and-delete

Thanks

Yogesh

MERGE is a newly introduced in sql server 2008.

Performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table

[ WITH  [,...n] ]
MERGE 
    [ TOP ( expression ) [ PERCENT ] ] 
    [ INTO ]  [ WITH (  ) ] [ [ AS ] table_alias ]
    USING  
    ON 
    [ WHEN MATCHED [ AND  ]
        THEN  ] [ ...n ]
    [ WHEN NOT MATCHED [ BY TARGET ] [ AND  ]
        THEN  ]
    [ WHEN NOT MATCHED BY SOURCE [ AND  ]
        THEN  ] [ ...n ]
    [  ]
    [ OPTION (  [ ,...n ] ) ]

;


At least one of the three MATCHED clauses must be specified, but they can be specified in any order. A variable cannot be updated more than once in the same MATCHED clause.
Any insert, update, or delete actions specified on the target table by the MERGE statement are limited by any constraints defined on it, including any cascading referential integrity constraints. If IGNOREDUPKEY is set to ON for any unique indexes on the target table, MERGE ignores this setting. 
The MERGE statement requires a semicolon (;) as a statement terminator. Error 10713 is raised when a MERGE statement is run without the terminator.
When used after MERGE, @@ROWCOUNT (Transact-SQL) returns the total number of rows inserted, updated, and deleted to the client.
MERGE is a fully reserved keyword when the database compatibility level is set to 100. The MERGE statement is available under both 90 and 100 database compatibility levels; however the keyword is not fully reserved when the database compatibility level is set to 90.

Permissions
Requires SELECT permission on the source table and INSERT, UPDATE, or DELETE permissions on the target table. For additional information, see the Permissions section in the SELECT, INSERT, UPDATE, and DELETE topics.

Example
 MERGE Production.UnitMeasure AS target
    USING (SELECT @UnitMeasureCode, @Name) AS source (UnitMeasureCode, Name)
    ON (target.UnitMeasureCode = source.UnitMeasureCode)
    WHEN MATCHED THEN 
        UPDATE SET Name = source.Name
    WHEN NOT MATCHED THEN	
        INSERT (UnitMeasureCode, Name)
        VALUES (source.UnitMeasureCode, source.Name);

                                        

MERGE Statement

MERGE statement allows us to insert, update, or delete data based on certain join conditions in the same statement. In previous versions of SQL Server, we have to create separate statements if you need to insert, update, or delete data in one table based on certain conditions in another table. With MERGE, you can include the logic for these data modifications in one statement. it inserts rows that don’t exist and updates the rows that do exist. With the introduction of the MERGE SQL command, developers can more effectively handle common data warehousing scenarios, like checking whether a row exists, and then executing an insert or update or delete.

The MERGE statement basically works as separate insert, update, and delete statements all within the same statement. You specify a "Source" record set and a "Target" table, and the join between the two. You then specify the type of data modification that is to occur when the records between the two data are matched or are not matched. MERGE is very useful, especially when it comes to loading data warehouse tables, which can be very large and require specific actions to be taken when rows are or are not present.

MERGE SQL statement improves the performance as all the data is read and processed only once whereas in previous versions three different statements have to be written to process three different activities (INSERT, UPDATE or DELETE) in which case the data in both the source and target tables are evaluated and processed multiple times; at least once for each statement.

The MERGE SQL command looks like as below:

MERGE <target_table> [AS TARGET]
USING <table_source> [AS SOURCE]
ON <search_condition>
[WHEN MATCHED 
THEN <merge_matched> ]
[WHEN NOT MATCHED [BY TARGET]
THEN <merge_not_matched> ]
[WHEN NOT MATCHED BY SOURCE
THEN <merge_ matched> ];

Exanple:

CREATE TABLE dbo.BookInventory  -- target
(
  TitleID INT NOT NULL PRIMARY KEY,
  Title NVARCHAR(100) NOT NULL,
  Quantity INT NOT NULL
    CONSTRAINT Quantity_Default_1 DEFAULT 0
);

CREATE TABLE dbo.BookOrder  -- source
(
  TitleID INT NOT NULL PRIMARY KEY,
  Title NVARCHAR(100) NOT NULL,
  Quantity INT NOT NULL
    CONSTRAINT Quantity_Default_2 DEFAULT 0
);

MERGE BookInventory bi
USING BookOrder bo
ON bi.TitleID = bo.TitleID
WHEN MATCHED THEN
  UPDATE
  SET bi.Quantity = bi.Quantity + bo.Quantity;

SELECT * FROM BookInventory;

Restrictions of using MERGE Statement

• The MERGE SQL statement requires a semicolon (;) as a statement terminator. Otherwise Error 10713 is raised when a MERGE statement is executed without the statement terminator.
• Any insert, update, or delete actions specified on the target table by the MERGE statement are limited by any constraints defined on it, including any cascading referential integrity constraints. If IGNOREDUPKEY is set to ON for any unique indexes on the target table, MERGE ignores this setting
• When used after MERGE, @@ROWCOUNT returns the total number of rows inserted, updated, and deleted to the client.
• At least one of the three MATCHED clauses must be specified when using MERGE statement; the MATCHED clauses can be specified in any order. However a variable cannot be updated more than once in the same MATCHED clause.
• The person executing the MERGE statement should have SELECT Permission on the SOURCE Table and INSERT, UPDATE and DELETE Permission on the TARGET Table.
• MERGE SQL statement takes same kind of locks minus one Intent Shared (IS) Lock that was due to the select statement in the ‘IF EXISTS’ as we did in previous version of SQL Server.
• For every insert, update, or delete action specified in the MERGE statement, SQL Server fires any corresponding AFTER triggers defined on the target table, but does not guarantee on which action to fire triggers first or last. Triggers defined for the same action honor the order you specify.
• Merging two very large tables (even if merging with a sub select) is not good
• Complex code that may require many operational changes
• MERGE DML operation only implements with single target table or view.Example of you can't insert, delete or update multiple tables using MERGE statement at once.
• Source table or MERGE source must has to be without duplicated records.
• difficulty in troubleshooting .

References

http://www.mssqltips.com/sqlservertip/1704/using-merge-in-sql-server-to-insert-update-and-delete-at-the-same-time/

http://www.sqlservercentral.com/articles/SQL+Server+2008/64365/

MERGE Statement

Performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.

Syntax / Construct

[ WITH <common_table_expression> [,...n] ]
MERGE 
    [ TOP ( expression ) [ PERCENT ] ] 
    [ INTO ] <target_table> [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias ]
    USING <table_source> 
    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 ] ) ]    
;

WITH

Specifies the temporary named result set or view, also known as common table expression, defined within the scope of the MERGE statement. The result set is derived from a simple query and is referenced by the MERGE statement.

TOP ( expression ) [ PERCENT ]

Specifies the number or percentage of rows that are affected. expression can be either a number or a percentage of the rows. The rows referenced in the TOP expression are not arranged in any order

[ INTO ]

Specifies the table name in which the records are inserted that are efffected by the Merge statement.

USING

Specifies the data source that is matched with the data rows in target_table based on . The result of this match dictates the actions to take by the WHEN clauses of the MERGE statement. can be a remote table or a derived table that accesses remote tables.

ON

Specifies the conditions on which is joined with target_table to determine where they match.

WHEN MATCHED THEN

Specifies that all rows of target_table that match the rows returned by ON , and satisfy any additional search condition, are either updated or deleted according to the clause.

WHEN NOT MATCHED [ BY TARGET ] THEN

Specifies that a row is inserted into targettable for every row returned by ON that does not match a row in targettable, but does satisfy an additional search condition, if present. The values to insert are specified by the clause. The MERGE statement can have only one WHEN NOT MATCHED clause.

WHEN NOT MATCHED BY SOURCE THEN

Specifies that all rows of target_table that do not match the rows returned by ON , and that satisfy any additional search condition, are either updated or deleted according to the clause.

The MERGE statement can have at most two WHEN NOT MATCHED BY SOURCE clauses. If two clauses are specified, then the first clause must be accompanied by an AND clause. For any given row, the second WHEN NOT MATCHED BY SOURCE clause is only applied if the first is not. If there are two WHEN NOT MATCHED BY SOURCE clauses, then one must specify an UPDATE action and one must specify a DELETE action. Only columns from the target table can be referenced in .

Restrictions

• The MERGE SQL statement requires a semicolon (;) as a statement terminator. Otherwise Error 10713 is raised when a MERGE statement is executed without the statement terminator.
• When used after MERGE, @@ROWCOUNT returns the total number of rows inserted, updated, and deleted to the client.
• At least one of the three MATCHED clauses must be specified when using MERGE statement; the MATCHED clauses can be specified in any order. However a variable cannot be updated more than once in the same MATCHED clause.
• Of course it’s obvious, but just to mention, the person executing the MERGE statement should have SELECT Permission on the SOURCE Table and INSERT, UPDATE and DELETE Permission on the TARGET Table.
• MERGE SQL statement improves the performance as all the data is read and processed only once whereas in previous versions three different statements have to be written to process three different activities (INSERT, UPDATE or DELETE) in which case the data in both the source and target tables are evaluated and processed multiple times; at least once for each statement.
• MERGE SQL statement takes same kind of locks minus one Intent Shared (IS) Lock that was due to the select statement in the ‘IF EXISTS’ as we did in previous version of SQL Server.
• For every insert, update, or delete action specified in the MERGE statement, SQL Server fires any corresponding AFTER triggers defined on the target table, but does not guarantee on which action to fire triggers first or last. Triggers defined for the same action honor the order you specify