Users often confuse a CTE with a temp table, but a CTE works like a view, and a CTE can be updated just like one. Matthew Spieth’s tip, with my notes.
SQL SERVER – Does Use of CTE Change the Order of Join in Inner Join
A reader said he uses a CTE to control join order. Does a CTE change the order of join in an inner join? Here is what I found.
Walking Foreign Key Chains to Find a Safe Delete Order
Map foreign key chains from a parent table with a recursive catalog query, then list deepest children first and flag cascade rules.
Building an Org Chart Query With hierarchyid
Build an org chart with hierarchyid, query descendants and levels, move a team safely, and compare it with recursive queries.
Translating Oracle CONNECT BY Queries to Recursive CTEs
Translate CONNECT BY into recursive CTEs with root selection, depth, paths, sibling ordering, cycle checks, and a recursion limit.
Common Table Expressions for Readability
Common table expressions for readability name query steps and support recursion, but they do not promise cached intermediate results.
A Bill of Materials With a Recursive CTE
Expand a bill of materials with a recursive CTE, multiply quantities across levels, trace component paths, and guard against cycles.