Partitioning

This is a very common thing to need to do and will put together the pieces of my last two posts into one large post in order to achieve the desired outcome.

Cleary this is a direct follow-up to my last post in which I showed how to merge a partition… this is the exact opposite in which we’ll split a partition into 2.

I’m going to briefly dip back into Partitioning for a few posts because this is something I was working on recently and I realized I didn’t have the code to hand on my blog… not that I rely on all code being present, but it’s definitely easier having something to hand than not.

This is a direct follow up from my last post about filegroups because without those we wouldn’t be able to perform what is called a Piecemeal database restore and, trust me, this is something you want to know how to do.

This is an alternative to native table Partitioning, and something I would potentially rather use instead whether I have Enterprise Edition or not.

I’ve covered Incremental Statistics over a couple of posts now and although they are a fantastic feature, there is a bug which you need to be aware of. Luckily it’s by no means a showstopper, more just an annoyance.

Based on my last post we now know how to create incremental statistics on a partitioned table and I mentioned that it was a massive improvement for performance of maintenance, but just how much?

I did a post a while ago about why you need to be careful with Sampled Statistics and therefore why a FULLSCAN is really the only reliable way to go… BUT there has always been the problem that a FULLSCAN update can take a VERY long time.


Oh, and yes… this is also to do with partitioning!!!

Yet more partitioning stuff… this time it’s the fact that we can rebuild an index on just a single partition of a table… and online. Now that’s handy!!!

To continue with partitioning there’s another good feature to consider which involves combining it with another Enterprise feature… compression.

This is something I struggled to put together the first time I needed it because partitioning uses internal tables with some very strange IDs and even stranger links between tables.

Partition Switching is an incredibly fast way to insert or delete data from a partitioned table and works very well in data warehousing environments, for example in which large overnight loads take place.

There are many reasons to use partitioning in order to make maintenance easier (stats and reindexing) and allowing for partition switching (fast loading and deleting of data), but there is another which is Partition Elimination.

Partitioning is an Enterprise only feature and, if you happen to be lucky enough to be using Enterprise, it can be an incredibly powerful and useful feature.