

In this scenario, it's important to ensure that all successive batches target new rows. Therefore, I/O performance is sometimes affected even when using the TOP clause to modify a large table by creating multiple batches. Without filters on the source table, the MERGE statement may perform a table scan or clustered index scan on the source table, as well as a table scan or clustered index scan of target table. Of these rows, 7 may be updated and 3 inserted, or 1 may be deleted, 5 updated, and 4 inserted, and so on. For example, specifying TOP (10) affects 10 rows. That is, there's no order in which the rows are distributed among the actions defined in the WHEN clauses.

The insert, update, or delete actions apply to the remaining joined rows in an unordered way. The TOP clause further reduces the number of joined rows to the specified value. The TOP clause applies after the entire source table and the entire target table join and the joined rows that don't qualify for an insert, update, or delete action are removed. For more information, see TOP (Transact-SQL). The rows referenced in the TOP expression aren't arranged in any order. expression can be either a number or a percentage of the rows. Specifies the number or percentage of affected rows. For more information, see WITH common_table_expression (Transact-SQL). The result set derives from a simple query and is referenced by the MERGE statement. Specifies the temporary named result set or view, also known as common table expression, that's defined within the scope of the MERGE statement. The semi-colon is required, or the query will return a syntax error.
