|
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 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: Then I add the new boundary point: Any help would be appreciated.
(comments are locked)
|
|
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. 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.
May 11 '12 at 04:43 PM
Gilbie
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
May 11 '12 at 04:58 PM
Gilbie
(comments are locked)
|
|
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?
May 11 '12 at 04:33 PM
Blackhawk-17
(comments are locked)
|

