Horizontal Partitioning for Big Tables

SQL Server Partition Tables
SQL Server 2017 supports up to 15,000 partitions by default. In versions earlier than SQL Server 2012, the number of partitions was limited to 1,000 by default

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.

Create Table
Create a new DB if needed or skip if on existing DB. Shown here to show no special feature needs 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

Create File Groups and Files
Add File Groups and Files. I’ve added four here. This is on my home pc with only one disk. Where possible, you would stripe the data files of your partitions across more than one disk to improve performance

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.

Create Partition Function and Scheme
Create Partition Function and Scheme: This is the main step. Note the Range Values in the function-these will determine which partition your data will sit.

The properties of the objects created can also be seen and scripted out in SSMS.

Server Objects
Server Objects: You can verify what you’ve created in SSMS by going to the properties.

Load some data into the tables and verify it’s partitioned correctly with a simple query.

table partitioning
Data placement: Notice you can query which partition each row is located

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.

Table partitioning
Query the tables-you can also query to confirm if a table is partitioned as well as how it is partitioned

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.

Adding Partitions
Adding a new partition is a breeze. Not how the new data falls within the new partition

Need to check the boundaries of each partition? As usual, there’s a query for that.


Archiving-Switching partitions

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.

Notice partition one has been switched to the new table and gone from the original table-Line 17

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.

Leave a Reply