Like when I started writing about Dates in SQL Server, a couple of notes on partitioning has turned into a series… And not one I’m so happy with (I had a lot to cover and at the same time a lack of a bigger picture direction). Rather than ramble on about aspects of partitioning in numerous posts, I thought I’d try just summarising some ideas all in one, with a few links to external reading.

When to partition, and how big?

Partitioning is as much about managing the data as anything else. In some database architectures, it can improve performance. In SQL Server, partitions don’t infer performance. I tried to write some “rule of thumb” notes, but couldn’t create anything as useful as this article: SQLskills SQL101: Partitioning (Kimberly L. Tripp).

A key point to note about partitions is the limitation on the number of partitions! SQL Server 2017 supports up to 15,000 partitions. Before that, it’s only 1,000! If you have one a day, you might get in trouble after only a few years.

Partitioned Tables & Views

Often, to get around the limit in the available number of partitions, or the fact that outside of enterprise SQL Server partitions weren’t a thing. Developers created partitioned views. Effectively, multiple tables (each representing a partition) and sticking them together using views.

• Partitioned Tables v. Partitioned Views–Why are they even still around?

Truncating & Moving Partitions

One key advantage of partitions is that they can be moved/switched. This allows a simple (and fast) statement to be executed, but doing a large movement of data. A key use for this is archiving (or dropping) partitions with minimum overhead on the table. See these links:

• SQL Server 2016 Truncate Table with Partitions
• Table Partitioning in SQL Server – Partition Switching