question

Gilbie avatar image
Gilbie 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
partitioningsplit
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Blackhawk-17 avatar image
Blackhawk-17 answered
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.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Gilbie avatar image Gilbie commented ·
Indeed a partition has been created but has it not been created on a filegroup that was being used by partition 3. What I am try to achieve is the creation of a new partition 4 on a new filegroup 4 which int values of 20 plus are written too.
0 Likes 0 ·
Gilbie avatar image Gilbie commented ·
Fantastic Blackhawk, your words have now shown me the light, thank you. The study book I am using makes no mention of not being able to split the over flow partition. (and yet it is the official MS Press) Regards Rob
0 Likes 0 ·
Gilbie avatar image
Gilbie answered
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
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Blackhawk-17 avatar image Blackhawk-17 commented ·
Is that not success? Hasn't an additional partition been introduced?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.