Lambda the Ultimate

Taking a simple set of data, we might have something like:

INSERT INTO Employees(empid, empname, mgrid) VALUES(7, "Chris", NULL)
INSERT INTO Employees(empid, empname, mgrid) VALUES(8, "Hank", 7)
INSERT INTO Employees(empid, empname, mgrid) VALUES(9, "Fred", 8)

The Anchor in the above query returns a single result:

(empid = 7, empname = "Chris", mgrid = NULL, lvl = 0)

Then this is fed into the join of the second query, returning another row:

(empid = 8, empname = "Hank", mgrid = 7, lvl = 1)

Which is fed into the recursion and then returns:

(empid = 9, empname = "Fred", mgrid = 8, lvl = 2)

Which then feeds into the final recursion where an empty set is returned:

(no results)

The final result set returned by the query would be the union of all the results:

(empid = 7, empname = "Chris", mgrid = NULL, lvl = 0)
(empid = 8, empname = "Hank", mgrid = 7, lvl = 1)
(empid = 9, empname = "Fred", mgrid = 8, lvl = 2)

As I said, it's not very consistent with any other SQL syntax that exists in T-SQL or PL/SQL.

Theoretically, the recursion shouldn't be much trickier than the PL/SQL CONNECT BY syntax. Problem is, though, that there are some other variations possible in the recursive CTE's arrangement. So certain assumptions in the connect by syntax may not hold in the CTE syntax.

That said, the links given for the new syntax really don't go into how MS implements the syntax nor what sort of optimizations they plan on using.