T-SQL Tuesday #118 – I wish SQL Server had better Table Partitioning

For this month’s T-SQL Tuesday, Kevin Chant asked us to dream up our fantasy SQL Server feature. I love this topic and so I had to join in on this as a late (and first-time) contributor.

My ask from Microsoft for SQL Server is something that…. Well… It should be simple. It should’ve been implemented YEARS ago. I’m thinking somewhere around the year 2005.

The feature I’m talking about is Table Partitioning, but… Better. A lot of great hopes were raised when SQL Server finally had table partitioning implementation (first introduced in SQL Server 2005). But, unfortunately, the feature by large remained the same way since, without any meaningful improvements (besides some parallelism and statistics maintenance improvements).

Meanwhile, Microsoft’s competitors took this feature more seriously, and implemented a lot of very interesting functionality around it.

Those types of functionality feel like a huge miss on Microsoft’s part, because I think they could bring huge benefit to SQL Server functionality.

So, let’s build a list of “sub-features” that we wish we had in SQL Server Table Partitioning. I’ll start with the following:

• Hash, List, Bucket Partitions (why only ranges? Check out this MySQL reference page, it’s got some really cool partition functionality!)
• Simple Partition Deletion (create a table… alter table switch to… drop table… merge partition… After all these years, why do we have to take all these extra steps for such a simple task??) [EDIT: Actually, TRUNCATE PARTITION was implemented on SQL 2016. Not exactly what I meant, but still a welcome improvement]
• Automatic Sliding Window (I mean come on. Oracle has Interval Partitioning for how long now?)
• Sub-Partitioning (this is a feature that, I kid you not, is implemented by almost every RDBMS vendor, except Microsoft!)
• Better Performance Optimization (there were a few improvements in recent versions, but performance is still a problematic issue when it comes to table partitioning in SQL Server. I’m sure there’s plenty to improve here)
• More Flexible Partition Indexing (what if we could create an index on some of the partitions? I think there could be great benefit in this)
• ALTER TABLE … MOVE PARTITION … TO FileGroup2 (imagine the possibilities if we had an easy-to-use command to change the filegroup of a certain partition… Archiving galore is what I think! If this operation could be online, even better!)

That’s all I got for now.

Do you have more ideas? Post them below!
Also, since I’m a rather late comer, I didn’t have the time to check whether there’s a feature request already for any of the above. So if you know of any such feature requests, post a link to them below in the comments.

Additional Resources

Check out the following reference pages about partitioning, from the other RDBMS competitors, just to see all the nifty stuff that Microsoft is missing out on:

• PostgreSQL – sub-partitions, list partitions, easier maintenance commands, and inheritance capabilities
• Oracle – sub-partitions, list and hash partitions, automatic partition creation, reference partitioning, and much more
• MySQL – sub-partitions, list, hash, columns and key partitions, and whole lot of stuff more