Changes to MERGE and HIERARCHYID

For those of you who already had the chance to play with the MERGE statement and the HIERARCHYID datatype in Microsoft SQL Server 2008, you might be interested to know that Microsoft made a few changes to those that will show up after CTP 6 (CTP Refresh and of course RTM).

Changes to MERGE

In CTP 6 the MERGE statement supports the following clauses (among others):

WHEN [TARGET] NOT MATCHED THEN

WHEN SOURCE NOT MATCHED THEN

Due to the efforts of Taylen Wong, Microsoft decided to change these two clauses. Taylen submitted to Microsoft that the existing syntax is ambiguous. Namely, that it is not really clear in the existing syntax which clause represents the case where a source row has no match in the target table, and which where a target row has no match in the source table.

In the next public CTP (CTP Refresh) and of course in RTM, Microsoft will change the syntax of the above clauses to the following nonambiguous  syntax:

WHEN NOT MATCHED [BY TARGET] THEN

WHEN NOT MATCHED BY SOURCE THEN

For example, consider the following MERGE statement that uses the CTP 6 syntax:

MERGE INTO dbo.Customers AS TGT

USING dbo.CustomersStage AS SRC

  ON TGT.custid = SRC.custid

WHEN MATCHED THEN

  UPDATE SET

    TGT.companyname = SRC.companyname,

    TGT.phone = SRC.phone,

    TGT.address = SRC.address

WHEN TARGET NOT MATCHED THEN

  INSERT (custid, companyname, phone, address)

  VALUES (SRC.custid, SRC.companyname, SRC.phone, SRC.address)

WHEN SOURCE NOT MATCHED THEN

  DELETE;

After CTP 6, the valid syntax for this statement will be:

MERGE INTO dbo.Customers AS TGT

USING dbo.CustomersStage AS SRC

  ON TGT.custid = SRC.custid

WHEN MATCHED THEN

  UPDATE SET

    TGT.companyname = SRC.companyname,

    TGT.phone = SRC.phone,

    TGT.address = SRC.address

WHEN NOT MATCHED BY TARGET THEN

  INSERT (custid, companyname, phone, address)

  VALUES (SRC.custid, SRC.companyname, SRC.phone, SRC.address)

WHEN NOT MATCHED BY SOURCE THEN

  DELETE;

Changes to HIERARCHYID

There were submissions to Microsoft asking to change the methods IsDescendant and Reparent of the HIERARCHYID datatype, reasoning that the existing naming of the methods in CTP 6 is problematic.

In CTP 6, the method IsDescndent is applied to an ancestor and accepts a child as input:

.IsDescendant()

It makes more sense that it would be the other way around. Microsoft accepted this reasoning and will change the method after CTP 6 to:

.IsDescendant()

As an example, consider the following query used in CTP 6 to return an employee and all of his/her subordinates in all levels:

SELECT C.empid, C.empname, C.lvl

FROM dbo.Employees AS P

  JOIN dbo.Employees AS C

    ON P.empid = 3

    AND P.hid.IsDescendant(C.hid) = 1;

After CTP, 6 this query should be revised to:

SELECT C.empid, C.empname, C.lvl

FROM dbo.Employees AS P

  JOIN dbo.Employees AS C

    ON P.empid = 3

    AND C.hid.IsDescendantOf(P.hid) = 1;

Also, the method Reparent in CTP 6 will be renamed to GetReparentedValue after CTP 6 since it doesn’t actually reparent a node, rather just returns a value that can be used to reparent a node.

For example, if you used the following code to reparent nodes in CTP 6:

UPDATE dbo.Employees

  SET hid = hid.Reparent(@old_root, @new_root)

WHERE @old_root.IsDescendant(hid) = 1;

You would need to revise it after CTP 6 as follows:

UPDATE dbo.Employees

  SET hid = hid.GetReparentedValue(@old_root, @new_root)

WHERE hid.IsDescendantOf(@old_root) = 1;

Cheers,

BG