Merge in Visual Basic .NET

Encoding Universal Product Code version A in Visual Basic .NET Merge

SQL Server 2008 introduces a new type of update operation called MERGE. MERGE is a hybrid of the other update operations and can be used to perform conditional changes to a table. The business value of this operation is that it can collapse multiple T-SQL operations into a single query. This simpli es the code that you have to write to modify tables, improves performance, and really helps operations against large tables that could be so large as to make multistep operations effectively too slow to be useful. Now that you have seen how the other update operations are handled, you might have gured out that MERGE is actually not a dif cult extension of the action column techniques used in the other operations. Like the other queries, the source data is scanned, ltered, and modi ed. However, in the case of MERGE, the set of rows to be changed is then joined with the target source to determine what should be done with each row. Based on this join, the action column for each row is modi ed to tell the STREAM UPDATE operation what to do with each row. In Listing 8-15, an existing table is going to be updated with new data, some of which might already exist in the table. Therefore, MERGE is used to determine only the set of rows that are missing. Figure 8-57 contains the resulting MERGE query plan.

CREATE GO INSERT INSERT INSERT GO CREATE GO INSERT INSERT INSERT GO TABLE AnimalsInMyYard(sightingdate DATE, Animal NVARCHAR(200)); INTO AnimalsInMyYard(sightingdate, Animal) VALUES ('2008-08-12', 'Deer'); INTO AnimalsInMyYard(sightingdate, Animal) VALUES ('2008-08-12', 'Hummingbird'); INTO AnimalsInMyYard(sightingdate, Animal) VALUES ('2008-08-13', 'Gecko'); TABLE NewSightings(sightingdate DATE, Animal NVARCHAR(200)); INTO NewSightings(sightingdate, Animal) VALUES ('2008-08-13', 'Gecko'); INTO NewSightings(sightingdate, Animal) VALUES ('2008-08-13', 'Robin'); INTO NewSightings(sightingdate, Animal) VALUES ('2008-08-13', 'Dog');

-- insert values we have not yet seen - do nothing otherwise MERGE AnimalsInMyYard A USING NewSightings N ON (A.sightingdate = N.sightingdate AND A.Animal = N.Animal) WHEN NOT MATCHED THEN INSERT (sightingdate, Animal) VALUES (sightingdate, Animal);

As MERGE plans tend to get a bit large, I ll split this into pieces and discuss each portion of the query plan. The rst part of the plan can be seen in Figure 8-58.

8

First, the source table NewSightings is read, and the query processor performs a probe into the target table AnimalsInMyYard to see if the row is already there. The Compute Scalar underneath the Left Outer Join exists merely to add a column that is 1 if the value was matched and, due to the nature of how Left Outer Joins work, returns a value of NULL if there is no matching row to match the source table row. The Compute Scalar above the join generates the Action column:

[Action1008] = Scalar Operator(ForceOrder(CASE WHEN [TrgPrb1006] IS NOT NULL THEN NULL ELSE (4) END))

In the upper half of this plan (shown in Figure 8-59), the lter eliminates rows that have a null action (Predicate: [Action1008] IS NOT NULL), as this MERGE statement only has one action (It is possible to have multiple operations within a single MERGE statement). The Spool provides Halloween Protection, which means that it consumes all rows from its inputs before attempting to write values back into the AnimalsInMyYard table. Table MERGE is really just an Update operation, but the showplan output has been changed to avoid confusion.

SQL Server also has special optimization logic to speed the execution of large batch changes to a table. If a large percentage of a table is being changed by a query, SQL Server can create a plan that avoids modifying each B+ tree with many individual updates. Instead, it can generate a per-index plan that determines all the rows that need to be changed, sorts them into the order of the index, and then applies the changes in a single pass through the index. This approach can be noticeably more ef cient than updating each row individually. These plans are called per index or wide update plans, as you see in their plan shape. The following example demonstrates a wide update plan. Figure 8-60 contains the resulting plan.

CREATE CREATE GO CREATE GO CREATE UPDATE TABLE dbo.update6(col1 INT PRIMARY KEY, col2 INT, col3 INT); INDEX i1 ON update6(col2); VIEW v1 WITH SCHEMABINDING AS SELECT col1, col2 FROM dbo.update6; UNIQUE CLUSTERED INDEX i1 ON v1(col1); update6 SET col1=col1 + 1;