Keeping historical data in tables forever – mission (im)possible!
summary
Emergency-control-room data must stay in same tables forever, be editable historically, and remain fast; operational tables grow 1–20M rows/year and logging can exceed 50M/year. Tests use SQL Server partitioning (clustered index with incident_date as leading key), aligned and non‑aligned nonclustered indexes, filtered nonclustered columnstore, partition switching to archive tables, and Redgate data generation; testing loads 3–4M rows. Partitioning gives partition elimination only when queries filter by date; Entity Framework broad selects force scans; aligned vs non-aligned indexes trade selectivity and maintenance; partition switch forbids non-aligned indexes and FK constraints.
AI-written summary · about
chapters (7)
- 0:00Start
- 2:04Data Management Strategy is needed
- 5:52Data usage and data volume
- 7:56Obvious solution - Let's Partition!
- 10:50Important rules for partitioning
- 40:21Operational Analytics Considerations Summary
- 42:23Partition Switch OUT
related talks
50mKeeping historical data in tables forever – mission (im)possible!
51mDealing with Very Large Tables in SQL Server
01h 04mFrom CRUD to Event Sourcing
24mDatabases beyond the tutorials- 51mDealing with Very Large Tables in SQL Server
54mLessons learned from refactoring multi terabyte databases for the last 10 years
47mConfessions of a Professional Data Hoarder
50mTime Series with SQL Server 2022