Recursive CTE Employee Hierarchy Query – Name Table

Task: You are a senior SQL database architect. Create a complete, production‑ready solution for retrieving an employee hierarchy using a recursive Common Table Expression (CTE). The solution must include the table definition, sample data, the recursive query, an example result set, a detailed explanation of how the query works, and performance recommendations (indexes, pitfalls, and alternatives).

Requirements:

1. Schema Definition – Provide the exact CREATE TABLE statement for an employees table that stores:

   • employee_id (primary key, integer, auto‑increment)
   • first_name (varchar)
   • last_name (varchar)
   • manager_id (integer, nullable, foreign key to employee_id)
   • hire_date (date)
   • salary (decimal(12,2))
   • department_id (integer)

2. Sample Data – Insert at least 12 rows that illustrate a multi‑level hierarchy (e.g., CEO → VP → Director → Manager → Individual Contributor). Include at least one case where an employee has no manager (top‑level) and one case with a circular reference to demonstrate how the query prevents infinite loops.

3. Recursive CTE Query – Write a single SQL statement that:

   • Starts from a given root employee (parameter @RootEmployeeId).
   • Returns each descendant with the following columns:
     • employee_id
     • full_name (concatenation of first and last name)
     • manager_id
     • level (1 for the root, 2 for direct reports, etc.)
     • path (a string showing the chain of employee IDs from the root to the current row, separated by ->)
     • salary
   • Limits recursion depth to 10 levels to avoid runaway queries.
   • Detects and excludes circular references, adding a column is_cycle (0 = normal, 1 = cycle detected).

4. Result Example – Show the output of the query when @RootEmployeeId = 1 (the CEO) using the sample data. Present the result as a markdown table.

5. Explanation – Provide a step‑by‑step walkthrough of the CTE, covering:

   • Anchor member
   • Recursive member
   • How level and path are built
   • How the cycle detection works (WHERE NOT EXISTS or OPTION (MAXRECURSION ...) etc.)
   • Why limiting recursion depth is important.

6. Performance Tips – List:

   • Recommended indexes (including covering indexes) for optimal execution.
   • Potential pitfalls (e.g., large hierarchies, missing manager records, deep recursion).
   • Alternative approaches (e.g., materialized path, nested set, hierarchyid) and when to prefer them.

7. Output Format – Deliver the answer in markdown with the following sections and headings exactly as shown:

   • ## 1. Schema Definition
   • ## 2. Sample Data
   • ## 3. Recursive CTE Query
   • ## 4. Result Example (Root Employee ID = 1)
   • ## 5. Explanation of the Query
   • ## 6. Performance Tips & Index Recommendations

Deliverable: A single, self‑contained markdown document that fulfills all the requirements above, ready to be copied into an SQL client or documentation repository. No additional commentary outside the specified sections.