Transit ops needs near-real-time lookups of active vehicles and wants a lean nonclustered index that ignores inactive fleet rows. Which index design fits?
Select an answer to reveal the explanation.
Short Explanation
A filtered index is a clipboard of “buses on the road right now,” not the entire retired fleet in the bone-yard. Transit lookups stay lean because inactive vehicles never clutter that civic index.
Full Explanation
Transit near-real-time lookups usually care about vehicles that are active now, not the entire retired fleet. A filtered nonclustered index on vehicle_id with a predicate such as IsActive = 1 stores only qualifying keys, shrinking the index and keeping seeks cheap for ops location queries. That selective structure matches the municipal requirement for a lean lookup path that ignores inactive rows while still supporting equality seeks on the vehicle identifier. A clustered columnstore on historical inactive vehicles optimizes analytical scans of the wrong population for live lookups. Full table scan hints that disable indexes deliberately worsen latency. A UNIQUE constraint on latitude is unrelated to activity filtering and would incorrectly forbid two buses sharing a depot coordinate. Caveat: filtered indexes only help queries whose predicates match (or are subsets of) the filter; ensure ops queries include the active predicate (or equivalent) so the optimizer can use the index. If activity flags flip frequently, monitor index maintenance and update patterns so the filter stays selective. Operational check: compare plans for an active vehicle_id lookup with and without the filtered index, and confirm the filtered index is chosen while a query for inactive vehicles does not misuse it.