No, this is not something announced at PASS Summit 2011. But it’s something that has been on my wish list for a long time.
Table partitioning is a great feature, and overwhelmingly, table partitioning is touted as a data warehousing feature for both sliding a partition into a table as a means of loading new data quickly and for sliding a partition out of a table as a means of purging old data quickly.
The pattern of the sliding-window data access, however, is not limited to data warehousing applications. For many OLTP applications, new data is constantly being inserted into a table from the upstream systems (e.g. mid-tier application servers). The focus of the application is mostly on the more recent data, and the older data in the table may be purged or archived to somewhere else. For an OLTP table, we probably would never slide any pre-populated partitions into the table. But we could benefit greatly from being able to slide a partition out of the table, relieving us of having to purge old data with a series of DELETE statements, a slow and painful process.
In the real-world, the table partitioning feature is not widely used on OLTP tables, at least, not on the systems I’m aware of. The main deterrent seems to be the amount of maintenance overhead in managing new partitions.
Come to think about the table partitioning feature. It is really a physical level concept. From the application or logical level, we want to have its benefits, but rarely if ever do we want to go through the laborious process in maintaining it. In other words, we’d like the feature to be transparent at the application and logical level.
For instance, why can’t we just say something like:
CREATE TABLE foo (c1 datetime …) WITH PARTITION ON c1 BY WEEK;
And expect the SQL Server engine to do the right thing in creating the partitions as necessary, and put data into the correct partitions. As long as we can completely express our intent in how the data should be partitioned in that table, we really don’t care how SQL Server does it internally as long as all the other table operations continue to behave as is. Of course, we’d like to be able to execute:
ALTER TABLE foo DROP PARTITION for <week>;
And expect the partition for that week be dropped as a metadata operation.
As a new week approaches, SQL Server knows that it needs a new partition to accommodate the data coming into the table, and will just automatically create one under the hood without bothering the human user. That’s total transparency, and would make the life a lot easier when it comes to purge old data from an OLTP table.
Yes, I know you can automate the process of managing new partitions with a script and you can probably pre-create enough partitions to be sufficiently safe. But building that transparency into the database engine as a feature relieves the user from having to worry about how robust our scripted automation may be.