SQL Fascination

I often see people asking whether dynamic table partitioning exists in SQL Server, or they provide a scenario that would effectively be asking the same question. So let’s get the easy answer out now – straight out of the box SQL Server has no dynamic partitioning.

To be fair, straight out of the box there is no tooling surrounding partitioning either except for a handful of DMV’s – if you want to automate a rolling window, then you need to program that yourself. SQL Server 2008 added a few bits; it struck me that if you need to use a wizard to turn an existing table into a partitioned table then your not really planning ahead.

So if it is possible to automate a rolling window system, surely it is possible to automate some kind of dynamic partitioning?

Well, that depends on what the definition of ‘dynamic partitioning’ is when it comes to SQL, which would normally be defined by the person who needs the feature to solve their specific issue. Before I start writing up a wish list of options and features to guide me hacking some SQL together to solve the problem – you have to ask; do you really need dynamic partitioning?

Table Partitioning by its nature suits larger volumes of data in a rolling window, where we migrate older data out and bring in new values. However, partitioning has been used for a variety of purposes that it possibly was not considered for originally such as:

• Performance gain through Partition Elimination
• Multi-Tenancy databases, placing each client in a separate partition

Bizarrely each of those reasons has a counter argument:

• Partition elimination only benefits queries that include the partition key in the where clause, otherwise it is detrimental to the query since it requires every partition is examined.
• Aside from the limit of 1000 partitions therefore 1000 customers, security is easier to compromise, upgrades per customer are not possible and the whole backup restore strategy for individual customers get’s very complex since you do not wish to restore the whole table but a single partition.

Back to the question, do we really need dynamic partitioning?

The complexity and scale of most partitioned tables indicates that they should not occur by ‘accident’, and retro-fitting a partitioned table indicates a lack of data modelling / capacity planning.  The ‘alternative’ reasons for partitioning, are amongst some of the drivers for the dynamic partitioning request.

To make best use of the partitioned table feature requires planning and design, in which case it does not need to be ‘dynamic’.

That all being said, in the coming posts I am going to write-up my wish list of features to start building a basic dynamic partitioning system and then make it more complex over time – it makes for a fun exercise.

If you have any thoughts on features you would want to see in it, just add them in a comment.