Hierarchical Locations, Sql Server

Language: Layout:

CREATE TABLE Location
(
	LocationID INT, 
	Name VARCHAR(50), 
	ParentLocationID INT, 
	LocationType VARCHAR(50)
);
    
INSERT INTO Location (LocationID, Name, ParentLocationID, LocationType)
VALUES
    (1, 'Planet Earth', NULL, 'Planet'),
    (2, 'Africa', '1', 'Continent'),
    (3, 'Antartica', '1', 'Continent'),
    (4, 'Asia', '1', 'Continent'),
    (5, 'Australasia', '1', 'Continent'),
    (6, 'Europe', '1', 'Continent'),
    (7, 'North America', '1', 'Continent'),
    (8, 'South America', '1', 'Continent'),
    (9, 'United States', '7', 'Country'),
    (10, 'Canada', '7', 'Country'),
    (11, 'Mexico', '7', 'Country'),
    (12, 'California', '9', 'State'),
    (13, 'San Diego', '12', 'City'),
    (14, 'England', '6', 'Country'),
    (15, 'Cornwall', '14', 'County'),
    (16, 'Truro', '15', 'City');		
    
    
DECLARE @LocationID INT = 7; -- NORTH AMERICA

WITH LocationCTE AS
(	SELECT	l.LocationID, l.Name, l.ParentLocationID, l.LocationType
	FROM	dbo.Location AS l	
	WHERE	LocationID = @LocationID
	UNION ALL
	SELECT	l.LocationID, l.Name, l.ParentLocationID, l.LocationType
	FROM	dbo.Location AS l
			INNER JOIN LocationCTE AS c
				ON c.LocationID = l.ParentLocationID
)
SELECT	*
FROM	LocationCTE;

View schema
Execution time: 0,02 sec, rows selected: 7, rows affected: 16, absolute service time: 0,19 sec