A permit fees table must archive old fiscal years quickly during year-end close. Which partitioning pattern should they use?
Select an answer to reveal the explanation.
Short Explanation
You can treat each fiscal year as a rolling shelf: when a year ages out, switch its partition to the archive instead of removing fees row by row. That keeps current permit fees available while making year-end close faster and more manageable.
Full Explanation
Partitioning the permit fees table by fiscal_year aligns its data with the year-end archival boundary. When a fiscal year ages out, you can switch that partition to an archive table, moving its rows without rebuilding the entire live table or deleting them individually. Keeping every fiscal year in one partition and rebuilding the whole table each December makes archival work heavier and more disruptive. Deleting aged rows one at a time in a cursor does not scale as well and can hold locks much longer than partition switching. Moving fee history exclusively into graph edge tables keyed by contractor name addresses relationship modeling rather than the fiscal-year lifecycle, so it does not provide the needed archival pattern. Exam caveat: partition switching requires compatible table and partition structures, aligned indexes, and a matching archive schema; plan these requirements before year-end close. As a practical check, test the switch on a copy of the table: confirm that the aged fiscal_year rows no longer appear in live-table queries and are present in the archive table.