asked
Partitioning & introducing new boundary points, partitions & filegroup
Hi I am currently studying for my MCTS 70-432. I am currently working on partitioning data, specifically adding a new boundary point to a new filegroup, I either have not interpreted how a Split Range works, correctly or I am getting the procedure wrong. I initially have 2 boundary points in my function (Left Range) which is on an int column, & my scheme is obviously on 3 filegroups. Therefore Partition1, filegroup 1 = lowerboundary = null & upperboundary = 5 Partition2, filegroup2 = lowerboundary = 5 & upperboundary = 10 Partition3, filegroup 3 = lowerboundary = 10 & upperboundary = null I first add a new filegroup with file, called Partition4, then add the new filegroup to the partition scheme & finally add the new boundary point 20. My partitions now return: Partition1, filegroup 1 = lowerboundary = null & upperboundary = 5 Partition2, filegroup2 = lowerboundary = 5 & upperboundary = 10 Partition3, filegroup 4 = lowerboundary = 10 & upperboundary = 20 Partition4, filegroup 3 = lowerboundary = 20 & upperboundary = null I don't understand why partition4 is on filegroup 3, I would have expected it to be on filegroup4, I therefore guess I am doing something wrong. Once I have created the new filegroup and file I run: ALTER PARTITION SCHEME MCTSPartitionsScheme NEXT used partition4 ; go Then I add the new boundary point: ALTER PARTITION FUNCTION MCTSPartitionsFunction() SPLIT RANGE (20); Any help would be appreciated. Regards Rob
You are trying to SPLIT outside of your stated boundaries. Try the same experiment but SPLIT on 7, which is less than 10. **EDIT** ---------- Having a bad week. Disregard my previous advice... that wouldn't make sense or Sliding Windows wouldn't work. I was reading too quick and working on too many things. SQL Server inserts the added FileGroup and redistributes the data. Since you are SPLITting the scheme it retains the FG that had the overflow and inserts the new one where the partition was inserted. So, if you start with FG1, FG2, FG3 and SPLIT in a new partition You end up with FG1, FG2, FG4, FG3. I imagine it's easier within the metadata to do this and/or SQL Server is coded to assume the last partition may have lots of extraneous data and is trying to limit the actual I/O that would be introduced and/or it's simpler linked list maintenance.
Nope sorry I now get: Part Lower Upper Filegroup 1 NULL 5 Partition1 2 5 7 Partition4 3 7 10 Partition2 4 10 NULL Partition3
Is that not success? Hasn't an additional partition been introduced?
5 People are following this question.
