SQL Server 2008+: Merge (Unity is Strength - Strengthen your Performance by Merge)
Problem Definition
In SQL Server for each DML transaction (Insert, Update, Delete) we need to write separate statements which will be executed one by one in SQL Server execution engine.
Let's give a basic example that, if we are passing a record to procedure, if the record already exists, then we need to update or else it should be inserted. In this scenario, we need to write 1 DML statement to check the record's existence and 2 DML statements based on the 1st query result, which will be a performance hurdle.
Solution
Simply we can say "Unity is strength". Like that "MERGE" your DML and DDL statements together to break the performance hurdles.
MERGE
MERGE is a new feature in SQL Server 2008 (and up) that provides an efficient way to perform multiple DML operations.
Example
Let's assume, we are going to write a procedure for this payroll scenario.
1. If employee exists and salary is less than 100, then delete that employee from payroll
2. If employee exists and salary is greater than 100, then update (add extra amount as salary / 100)
3. if employee does not exist, then insert the employee with salary 2500.
In prior to SQL Server 2008 version, we would need to write multiple DML statements.
But in 2008 (and up) we can use MERGE as follows: (The details of the query added as comments.)
DECLARE @PayRoll TABLE(EmpID INT, Salary INT, AddAmt INT) -- Declare table
INSERT INTO @PayRoll
VALUES(1,2500,0),(2,100,0),(3,2700,0) --Insert sample records
MERGE @PayRoll AS PayRoll
USING (SELECT EmpID,Salary FROM @PayRoll) AS PayRollAd -- Update the payroll table by taking values from that same table
ON PayRoll.EmpID = PayRollAd.EmpID
WHEN MATCHED AND PayRoll.Salary < 100 THEN DELETE -- If employee salary less than 100 then delete
WHEN MATCHED THEN UPDATE SET PayRoll.AddAmt = (PayRoll.Salary / 10) -- If employee salary greater than 100 then update the additional amount
WHEN NOT MATCHED THEN
INSERT(EmpID, Salary)
VALUES (PayRollAd.EmpID,2500); -- if employee is not there then insert (this scenario will come only when we use different table for merge)
SELECT * FROM @PayRoll
Points to note
Semicolon is mandatory after the merge statement. This is very important and it is an often forgotten fact!
When there is a MATCH clause used along with some condition, it has to be specified first among all other WHEN MATCH clauses.
Conclusion
MERGE statement improves the performance of database as it passes through data only once.
References