Partitioned Tables: Rolling/Rotating/Round-Robining Partitions
Category:
database-administration
I recently received a terrific question about options for “rotating” table partitions.
Tag: table-partitioning
New Course: Why Table Partitioning Does Not Speed Up Query Performance – With One Exception
Category:
course-announcements
Table Partitioning is Tricky Business
In this new course you will learn why SQL Server’s table partitioning feature won’t make your queries against disk-based rowstore indexes faster– and may even make them slower.
Adding Partitions to the Lower End of a Left Based Partition Function
Category:
tsql-programming
I recently got a table partitioning question from a reader:
We now need to load some historical data into the table for 2013 so I want to alter the function and schema to add monthly partitions for this. But I can’t work out how to do this using SPLIT? Every example and tutorial I’ve looked at shows how to add new partitions onto the end of a range, not split one in the middle.
Understanding Left vs. Right Partition Functions (with Diagrams)
Category:
tsql-programming
You’re designing table partitioning, or you want to make a change to an existing partition function. It’s critical to understand the difference between how “left” and “right” partition functions behave, but the documentation is a bit confusing on this topic.
Which Filegroup is that Partition Using? How Many Rows Does It Have?
Category:
tsql-programming
Table Partitioning in SQL Server has a bit of a learning curve. It’s tricky to just figure out how much data you have and where the data is stored.
SPLIT in a LEFT Partition Function: Where Does the Above-Boundary Data Go?
Category:
tsql-programming
Table partitioning seems simple, but there’s a lot of complexity in designing and managing it if you decide to use filegroups and splitting.
When you first implement partitioning in this scenario, you decide where you’re going to keep “out of bound” data when you create your partition scheme. Be careful when you make that decision, because it may not be easy to change later.
Merging Boundary Points: Does a Changing Partition_Number Indicate Data Movement?
Category:
database-administration
I received a question from a reader who was testing out a partitioning architecture:
We are testing table partitioning using one filegroup per partition. When we merge a boundary point, we see that partition_number changes in sys.partitions. Does this mean that data movement is occurring?