{"id":293,"date":"2019-04-27T18:00:23","date_gmt":"2019-04-27T18:00:23","guid":{"rendered":"https:\/\/datablog.roman-halliday.com\/?p=293"},"modified":"2019-06-11T06:18:51","modified_gmt":"2019-06-11T06:18:51","slug":"partitions-in-sql-server-a-collection-of-thoughts","status":"publish","type":"post","link":"https:\/\/datablog.roman-halliday.com\/index.php\/2019\/04\/27\/partitions-in-sql-server-a-collection-of-thoughts\/","title":{"rendered":"Partitions in SQL Server: A collection Of Thoughts"},"content":{"rendered":"

Like when I started writing about Dates in SQL Server<\/a>, 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.<\/p>\n

When to partition, and how big?<\/h1>\n

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<\/a> (Kimberly L. Tripp).<\/p>\n

A key point to note about partitions is the\u00a0limitation 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.<\/p>\n

Partitioned Tables & Views<\/h1>\n

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.<\/p>\n