Recursive CTE Terminology

Transcription of Video:

Every recursive CTE needs an anchor query. And what an anchor query is, is it defines the start of the recursion. This is the part that cannot reference the CTE itself, it has to start with some base starting point. So if you’re doing a hierarchy of departments, well, you’re starting the recursion may be giving me the top level departments. And then the recursive part may be for every top level department, give me everything, that’s the department below it. And then with this, you can have one or more anchor queries. Then you’ve got the recursive query. This is the part that repeats, and this is the part that references the CTE itself, and can repeat many, many, many levels of depth. And you can have one or more recursive queries. So for instance, if you were doing like a family tree structure, you might have select all of the people at the lowest level. And then for each of those persons, you may have one query that selects the mother’s side of the family, and another query that selects the father’s side of the family. And then those recursive query would would run and run that for every one in the result set. And you’d end up looking up all of the original starting people and then all of their mothers and all their fathers on both sides until there’s no data to return or until you hit the max recursion level. So Max recursion, it’s the number of times repeat the recursive query depth. The default is 100. And a max recursion of zero implies no maximum. Just keep going until there’s no results or until the server runs out of memory.

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!