x

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

more ▼

asked May 11, 2012 at 02:19 PM in Default

Gilbie gravatar image

Gilbie
92 4 4 5

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.
more ▼

answered May 11, 2012 at 03:22 PM

Blackhawk-17 gravatar image

Blackhawk-17
11.9k 28 31 37

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, 2012 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, 2012 at 04:58 PM Gilbie
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered May 11, 2012 at 03:55 PM

Gilbie gravatar image

Gilbie
92 4 4 5

Is that not success? Hasn't an additional partition been introduced?
May 11, 2012 at 04:33 PM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x53
x23

asked: May 11, 2012 at 02:19 PM

Seen: 1645 times

Last Updated: May 11, 2012 at 06:13 PM