Microsoft SQL Server 2008 R2 Unleashed- P162:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 1554 CHAPTER 42 What s New for Transact-SQL in SQL Server 2008 The combination of WHEN clauses specified in the MERGE statement determines the join method that SQL Server will use to process the query see Table . TABLE Join Methods Used for WHEN Clauses Specified WHEN Clauses Join Method WHEN MATCHED clause only INNER JOIN WHEN NOT MATCHED BY TARGET clause but not the WHEN NOT LEFT OUTER JOIN from MATCHED BY SOURCE clause source to target WHEN MATCHED clause and the WHEN NOT MATCHED BY SOURCE RIGHT OUTER JOIN from clause but not the WHEN NOT MATCHED BY TARGET clause source to target WHEN NOT MATCHED BY TARGET clause and the WHEN NOT MATCHED BY SOURCE clause FULL OUTER JOIN WHEN NOT MATCHED BY SOURCE clause only ANTI SEMI JOIN To improve the performance of the MERGE statement you should make sure you have appropriate indexes to support the join columns between the source table and target table. Any additional columns in the source table index that will help to cover the query may help improve performance even more for information on index covering see Chapter 34 Data Structures Indexes and Performance . The indexes should ensure that the join keys are unique and if possible sort the data in the tables in the order it will be processed so additional sort operations are not necessary. Unique indexes supporting the join conditions for the MERGE statement will improve query performance because the query optimizer does not need to perform extra validation processing to locate and update duplicate rows. To better understand how the MERGE statement works let s look at an example. First you need to set up some data in a source table. In the bigpubs2008 database there is a table called stores. For this example let s assume you want to set up a new table that keeps track of each store s inventory to support an application that can monitor each store s inventory and send notifications when certain items run low as well as to support the ability of each store to .
