Courts analysts need to walk hierarchical case–motion relationships for a single docket number in T-SQL. Which construct should they use?
Select an answer to reveal the explanation.
Short Explanation
A recursive CTE is the family-tree walk for motions hanging under a docket—start at the root and keep stepping to children. Regex counts and OPENJSON do not build that relationship spine.
Full Explanation
Courts analysts who must walk hierarchical case–motion relationships for a single docket number need a recursive traversal over parent–child links. A recursive common table expression (CTE) anchors on the docket root (or root motions) and repeatedly joins to child motions until the hierarchy is exhausted, returning the tree or forest for that docket in T-SQL without client-side graph walking.
OPENJSON shreds JSON arrays and objects; alone it does not encode hierarchical case relationship logic stored as relational parent keys. REGEXP_COUNT on docket captions can tally pattern matches in text but cannot invent trustworthy parent–child case links. A filtered index may speed predicates; it does not store or walk a case–motion hierarchy as a compressed bitmap substitute for recursion.
Exam caveat: recursive CTEs need a clear termination condition and guardrails against cycles; MAXRECURSION and cycle detection matter on messy legacy dockets. Operational check: load a small docket with known parent–child motions, run the recursive CTE filtered to that docket number, and verify depth and child counts match the hand-drawn tree before using it on production volumes. Model the hierarchy with an explicit parent_motion_id (or equivalent) rather than parsing captions; recursion over keys is stable, recursion over text is not. For deep dockets, materialize a closure or path enumeration only if reporting repeatedly needs ancestor filters that the recursive CTE cannot meet on time.