CODEWITHSUNDEEP
sql-server-2008partitioning
PseudoToad
PseudoToad

Reputation: 1574

Adding A Filegroup To An Existing Partition Schema

From BOL, I can see that the only way to ALTER a PARTITION SCHEME is to set the NEXT USED flag. This doesn't seem to add a new filegroup to the schema (or at least its not visible when I script out the schema as DROP/CREATE). Is it possible to add a new filegroup to an existing SCHEME or does this have to be done at initial creation?

Upvotes: 0

Views: 3846

Answers (1)

2lazydba
2lazydba

Reputation: 398

I tried using altering the scheme by using NEXT USED command and when I scripted the scheme the new FG was mentione in the script.

I think you might not have added any data which would reside in the new FG which is why it was not mentioned in the scheme script.

Here is the complete example.

Select * from sys.filegroups go

CREATE PARTITION FUNCTION myRangePF1 (int) AS RANGE LEFT FOR VALUES (1, 100, 1000); GO

CREATE PARTITION SCHEME myRangePS1 AS PARTITION myRangePF1 ALL TO ( [PRIMARY] );

CREATE TABLE test (col1 int primary key , col2 int ) on myRangePS1(col1) go

Insert into test Select 2 , 1 union Select 20,2 union Select 200, 3 union Select 2000, 4

GO

ALTER PARTITION SCHEME MyRangePS1 NEXT USED [SECONDARY];

GO

ALTER PARTITION FUNCTION myRangePF1 () SPLIT RANGE (1500);

--Script the scheme -- The second from last FG is Secondary CREATE PARTITION SCHEME [myRangePS1] AS PARTITION [myRangePF1] TO ([PRIMARY], [PRIMARY], [PRIMARY], [Secondary], [PRIMARY]) GO

Upvotes: 2

Related Questions