My knowledge is in DB2 specifically but looking at the explain diagrams seems to be the same with SQL Server.

The plan comes from here: https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=5ce9c241732ce6d05041a261061f6412

See it on Paste the Plan

The optimizer does not literally run a union all for each recursive query. It takes the structure of the query and assigns the first part of the union all to an "anchor member" then it will run through the second half of the union all (called the "recursive member" recursively until it reaches the limitations defined. After the recursion is complete, the optimizer joins all records together.

The optimizer just takes it as a suggestion to do a pre-defined operation.

My knowledge is in DB2 specifically but looking at the explain diagrams seems to be the same with SQL Server.

The plan comes from here:

See it on Paste the Plan

The optimizer does not literally run a union all for each recursive query. It takes the structure of the query and assigns the first part of the union all to an "anchor member" then it will run through the second half of the union all (called the "recursive member" recursively until it reaches the limitations defined. After the recursion is complete, the optimizer joins all records together.

The optimizer just takes it as a suggestion to do a pre-defined operation.

My knowledge is in DB2 specifically but looking at the explain diagrams seems to be the same with SQL Server.

The plan comes from here: https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=5ce9c241732ce6d05041a261061f6412

The optimizer does not literally run a union all for each recursive query. It takes the structure of the query and assigns the first part of the union all to an "anchor member" then it will run through the second half of the union all (called the "recursive member" recursively until it reaches the limitations defined. After the recursion is complete, the optimizer joins all records together.

The optimizer just takes it as a suggestion to do a pre-defined operation.

My knowledge is in DB2 specifically but looking at the explain diagrams seems to be the same with SQL Server.

The plan comes from here: https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=5ce9c241732ce6d05041a261061f6412

See it on Paste the Plan

The optimizer does not literally run a union all for each recursive query. It takes the structure of the query and assigns the first part of the union all to an "anchor member" then it will run through the second half of the union all (called the "recursive member" recursively until it reaches the limitations defined. After the recursion is complete, the optimizer joins all records together.

The optimizer just takes it as a suggestion to do a pre-defined operation.