ZIP File: TablePartitioningTool.zip
This experimental toolset is a result of my desire to have a simple repeatable solution for a complex problem: Table Loading and Partitioning Management for time based event data.
1. Loading can be a sore spot in any ETL pipeline when it is too slow. This results in an ever-increasing backlog of import data.
2. Partitioning within SQL Server 2005 is an extremely powerful and essential feature for dealing with large tables, but as a result the partitioning logic can be very complex to write at times.
3. Each time you create a partitioning and loading process for a given table you may implement each one differently. This creates unpredictable behavior of your overall service, application, or warehouse thus making it more costly to maintain and improve.
By using a code-generating approach for your loading and partitioning needs, you can quickly create solutions throughout your company. This solution offers the added bonus of creating a general standard to handle this complex problem, thus lowering your company’s overall cost of ownership.
1. Creates complex stored procedures which handle all the loading, partitioning, switching, and merging logic to the specifications of a given table template.
2. Provides table loading management
- Ensures minimum transaction logging
- Manages micro-loading*
- Keeps the table “Online” while loading the table
- Manages explicit time-based partitioning at grains of Hour, Day, Week, Month, or Year for any given table
- Manages rolling off partitions older than a configurable maximum