SG Hierarchy Document question

script.sql

-- This script can be ran repeatedly on an instance of Microsoft SQL Server.
-- Objects are created in tempdb.

----------------
-- Background --
----------------

-- The document titled 'SG Hierarchy Document' contains the following excerpt:
--
-- Common Table Expression
-- The recent MS SQL feature, that provides exposition of an hierarchy, that exists in the data, which is stored in primitive RecordId based files. It
-- is laborious and slow, both to code (complexity) and to execute (temporary tables; etc). Completely unnecessary if one implements the ordinary
-- Relational hierarchy that preceded CTE by decades, as documented herein.
--
-- Document link:
-- https://www.softwaregems.com.au/Documents/Student%20Resolutions/Hierarchy.pdf

--------------
-- Question --
--------------

-- The excerpt above contains the phrase 'Completely unnecessary'.
-- So how does hierarchy exposition / explosion work without a CTE?
-- Are you willing to share the relevant Microsoft SQL Server T-SQL?
--
-- I've provided my implementation below using an abridged version
-- of the family tree of Walt Disney.
-- This would be the go-to approach in the world of MS SQL Server,
-- and I suspect that a seeker on a Stack Overflow question would
-- be given similar CTE-based code if they asked.
--
-- But I'm totally in the dark as to how else you might get a similar
-- result. Thus the question.

use tempdb;
go

drop table if exists Progeny;
drop table if exists Person;
go

create table Person (
	PersonNo int not null,
	LastName char(40) not null,
	FirstName char(40) not null,
	BirthYear int not null,

	constraint Person_pk
	primary key clustered (PersonNo)
);

create table Progeny (
	ParentNo int not null,
	ChildNo int not null,

	constraint Progeny_pk
	primary key clustered (ParentNo, ChildNo),

	constraint Parent_Produced_Progeny_fk
	foreign key (ParentNo)
	references Person (PersonNo),

	constraint Child_IsProductOf_Progeny_fk
	foreign key (ChildNo)
	references Person (PersonNo)
);

insert into Person (PersonNo, LastName, FirstName, BirthYear)
values
	(1, 'Disney', 'Walter', 1901),
	(2, 'Disney', 'Lillian', 1899),
	(3, 'Disney-Miller', 'Diane', 1933),
	(4, 'Miller', 'Ronald', 1933),
	(5, 'Miller', 'Christopher', 1954),
	(6, 'Miller', 'Joanna', 1956);

insert into Progeny (ParentNo, ChildNo)
values
	-- Walt / Lillian produced Diane
	(1, 3),
	(2, 3),
	-- Diane / Ronald produced Christopher
	(3, 5),
	(4, 5),
	-- Diane / Ronald produced Joanna
	(3, 6),
	(4, 6);


-- Common Table Expression that shows Walt Disney's descendants
-- (using single anchor row)
with WaltDescendants as (
	select
		p.*,
		cast('' as char(3)) as ParentNo,
		cast('' as char(40)) as ParentLastName,
		cast('' as char(40)) as ParentFirstName,
		cast('' as char(4)) as ParentBirthYear,
		cast(1 as bit) as IsAnchorRow
	from Person p
	where p.PersonNo = 1

	union all

	select
		p.*,
		cast(c.PersonNo as char(3)),
		cast(c.LastName as char(40)),
		cast(c.FirstName as char(40)),
		cast(c.BirthYear as char(4)),
		cast(0 as bit) as IsAnchorRow
	from
		WaltDescendants c,
		Person p,
		Progeny pr
	where
		pr.ParentNo = c.PersonNo
		and p.PersonNo = pr.ChildNo
)
select *
from WaltDescendants;


-- Common Table Expression that shows everyone's descendants.
-- (Using multiple anchor rows)
with AllDescendants as (
	select
		p.*,
		cast('' as char(3)) as ParentNo,
		cast('' as char(40)) as ParentLastName,
		cast('' as char(40)) as ParentFirstName,
		cast('' as char(4)) as ParentBirthYear,
		cast(1 as bit) as IsAnchorRow
	from Person p

	union all

	select
		p.*,
		cast(c.PersonNo as char(3)),
		cast(c.LastName as char(40)),
		cast(c.FirstName as char(40)),
		cast(c.BirthYear as char(4)),
		cast(0 as bit) as IsAnchorRow
	from
		AllDescendants c,
		Person p,
		Progeny pr
	where
		pr.ParentNo = c.PersonNo
		and p.PersonNo = pr.ChildNo
)
select distinct *
from AllDescendants;