Recursive CTE Employee Hierarchy Query – Cycledetected Advanced

Task: Create a complete, production‑ready SQL script that uses a recursive Common Table Expression (CTE) to generate an employee hierarchy from a single “Employees” table. The script must handle arbitrary depth, detect and prevent cycles, calculate additional hierarchy metrics, and be fully documented.

Requirements:

1. Schema Assumption The table Employees has the following columns (you may add indexes if needed):

   • EmployeeID (INT, PK)
   • ManagerID (INT, nullable, FK to EmployeeID) – NULL for top‑level executives
   • EmployeeName (VARCHAR)
   • JobTitle (VARCHAR)
   • Salary (DECIMAL(12,2))
   • HireDate (DATE)

2. Recursive CTE Output Columns The final result set must include:

   • EmployeeID
   • EmployeeName
   • JobTitle
   • ManagerID
   • HierarchyLevel (1 for top‑level, 2 for direct reports, etc.)
   • Path (a string showing the chain of EmployeeName from the root to the current row, separated by “ >”)
   • SubordinateCount (total number of direct + indirect reports under the employee)
   • CumulativeSalary (sum of salaries of the employee and all of their subordinates)
   • CycleDetected (YES/NO flag indicating whether the row participates in a cycle)

3. Cycle Detection

   • The CTE must stop recursion when a cycle is detected and set CycleDetected = 'YES' for all rows involved in the cycle.
   • For non‑cyclic rows, CycleDetected = 'NO'.

4. Performance Considerations

   • Include an OPTION (MAXRECURSION 0) (or the equivalent for the target RDBMS) to allow unlimited depth.
   • Suggest appropriate indexes (e.g., on ManagerID) and any necessary statistics hints.

5. Documentation

   • Add clear, line‑by‑line comments explaining the purpose of each part of the script.
   • Provide a brief “How it works” section after the query, describing the recursion logic, the calculation of SubordinateCount and CumulativeSalary, and the cycle‑handling mechanism.

6. Sample Data & Expected Output

   • Supply INSERT statements for at least 8 employees that illustrate:
     • A normal hierarchy (CEO → VP → Manager → Staff)
     • A branch with multiple direct reports
     • A deliberate cycle (e.g., Employee 7 reports to Employee 8, and Employee 8 reports back to Employee 7)
   • Show the expected result set (as a markdown table) for the sample data, highlighting the HierarchyLevel, Path, SubordinateCount, CumulativeSalary, and CycleDetected columns.

7. Compatibility

   • Write the script for Microsoft SQL Server (T‑SQL). If you use any syntax that is specific to another RDBMS, note the required changes.

Expected Output Format:

1. SQL Script – a single code block fenced with triple backticks (```sql).
2. Explanation – a plain‑text section titled “How it works” with bullet points.
3. Sample Data – INSERT statements inside a fenced SQL block.
4. Result Example – a markdown table showing the query output for the sample data.

Deliver the entire answer in the order listed above, ensuring the script can be copied and run unchanged on a fresh SQL Server database.