A 311 triage board must rank open tickets by age within each neighborhood. Which T-SQL feature should produce that ranking?
Select an answer to reveal the explanation.
Short Explanation
Window ranking is like lining up tickets in each neighborhood’s own queue—oldest first—without collapsing the rest of the row. A citywide SEQUENCE cannot give you per-neighborhood triage order.
Full Explanation
A 311 triage board that ranks open tickets by age within each neighborhood needs ranking that restarts per partition. Window functions such as RANK, DENSE_RANK, or ROW_NUMBER with PARTITION BY neighborhood and ORDER BY ticket age (or opened_at) attach a triage order to each ticket while preserving the full row—exactly what a board UI needs to sort oldest-first inside every neighborhood slice.
A CHECK constraint can validate static predicates at insert; it does not assign dynamic age-based ranks that change as tickets age. A SEQUENCE that globally numbers tickets ignores neighborhood partitions and cannot express per-neighborhood triage order. An external table definition that ranks tickets only in blob metadata is outside the transactional triage query and does not compute neighborhood-partitioned ranks in T-SQL.
Exam caveat: ROW_NUMBER gives unique positions even for ties; RANK/DENSE_RANK preserve tie semantics—choose based on whether equal-age tickets may share a rank. Operational check: create two neighborhoods with mixed ticket ages, apply the window function, and confirm ranking restarts at 1 in each neighborhood and orders by age as specified. Apply the window in a query or view feeding the triage board so ranks refresh as tickets age or close without storing stale static ranks on the ticket row. Filter to open tickets before ranking if closed items should not consume triage positions within the neighborhood partition.