Difference between multiple CTE and recusrive CTE

Question

I am looking at different CTE examples online since I am trying to learn CTE but I a came across two primary ways to write CTEs

1. Recursive CTE which has an anchor and then UNIONs with the child query which uses the results from anchor and previous calls
2. Multiple CTE where there are two distinct CTEs defined comma saperated however, the second one ends up referring to the first one which is kind of like the Recursive CTE.

I am referring to an example below:

http://blog.sqlauthority.com/2009/08/08/sql-server-multiple-cte-in-one-select-statement-query/

and wondering how is it different than a Recursive with UNION and a single CTE name?

Answer 1

CTEs don't have to be recursive. You can use them instead of sub-queries which is what the first example is doing. You can have one or many CTEs in the place of sub-queries.

The best way to understand CTEs is to create some test data and run the two queries against them to see how the results differ.

Answer 2

A recursive CTE builds out its anchor set from within the derived CTE expression, usually based on the WHERE, etc. limiting conditions. The recursion is self-contained, as opposed to having dependencies on other, earlier-defined expressions/sets.

Assuming by multiple CTEs, you mean a set of CTEs in series defined by subsequent SELECT statements, then this is, by comparison, a set of transformations against the anchor sets (and fields) in order of definition.