It’s fairly common knowledge amongst database professionals partitioned tables can help considerably with performance in various situations but my guess is not many would have considered the enormous added benefits when it comes to archiving, a necessity with today’s big tables. The time you can save during both design and run time (including maintenance) is considerable, a message Microsoft hammers home at every opportunity. Given the enormous benefits and proliferation of the big tables as well as my observation this was another underutilised feature of the platform, I thought I’d share my experience of this feature. As a side note, my own experience is there often is hesitation amongst developers to touch anything in the database file groups (or often simply just off limits due to policy) and suspect this largely arises from the blur in roles and responsibilities between DBAs and Developers.
Regardless, it needs to be noted partitioned tables are only available on the Enterprise and Developer versions. Many attempts have been made to compare this with partitioned views for the benefits of those not on Enterprise but frankly, the benefits discussed here are simply not there, though partitioned views do offer other benefits.
There’s some coding involved so I’ll break down each step for those that don’t code for a living but will still benefit from a working understanding. As usual, there is more to partitioning than what’s covered here including indexing and table compression. Further reading is well advised-the official documentation is a good starting point.
Partitioning works on any database, no special feature need to be enabled.
I’ve arbitrarily added four file/file groups here for this demo.In practice, Microsoft guidelines should serve a good starting point for the number of partitions you should use
The main parts: partition function, partition scheme and partition table. Note the left range function used which sets the boundaries of the partition-you’re not restricted to dates or left side function but the partitioning column must be part of the primary key. Our table will now use the four file/file groups we created in step two for storage as opposed to the default.
The properties of the objects created can also be seen and scripted out in SSMS.
Load some data into the tables and verify it’s partitioned correctly with a simple query.
You can also check via SQL if a table is partitioned. The alternative (not shown) is to look at the properties of the table within SSMS.
Want to add a new partition? Not a problem, it’s only a few lines. Note the new partition and new records in row 5 and 6 after the insert.
Need to check the boundaries of each partition? As usual, there’s a query for that.
The cool bit, partition switching and it’s dead simple. Another year has passed and you don’t need 2010 data in the active table (actually anything before 2016-on partition one) and so that partition can be archived to improve performance by making the table smaller. You simply switch partitions to another table (dbo.WebsiteHitsArchive) as seen below.
I’ve shown switching to another partitioned table below but you could well switch to a non partitioned table. The other approach is transferring an entire non partitioned table to a partitioned table. All three approaches are identical, just slight syntax difference-Google the syntax, it’s not worth repeating here.
As you can see, it’s pretty simple and elegant, the whole approach as Microsoft engineers intended. It is also considerably faster than manual row operations (INSERT…SELECT FROM) and will not lock your table during the archive process. Just pluck lines 16 to 18 into a job and it’ll run/archive automatically on schedule following a conventional industry approach.
And there you have it, another reason why I love working on the Microsoft platform. Simple, powerful and continuously improving with each new release.