Hello there - really need help with a design issue for partitioning. Almost all examples I've seen uses dates, but in this case, I'm partitioning based on a Part ID or group of Part IDs and having problems figuring out how to add a new filegroup to a scheme properly upon the movement of a set of data to a new partition.
So I've got a table that I want to partition:
CREATE TABLE dbo.Transactions
(Transaction_ID [bigint] IDENTITY(1,1) NOT NULL
,Part_ID bigint NOT NULL
.
.
,PartitionColumn [int] NULL
)
PartitionColumn is a new column that I just created that is an integer. For some part numbers, I want those records in a separate partition. So for example, Part_ID 123, 456, and 789 I want in partitions 1000, 1001, and 1002, while the other Part_IDs I want in a set of random 10 partitions, let's say based on a formula (Part_ID % 10). (The population of this column for new inserted rows is another headache that I'll deal with later).
So I've created my filegroups and files. Let's call the filegroups FG_0 thru FG_9, and FG_123, FG_456, and FG_789.
So creating the partition function and partition is simple enough:
CREATE PARTITION FUNCTION Transaction_PF (int)
AS RANGE LEFT FOR VALUES (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 1000, 1001, 1002);
CREATE PARTITION SCHEME Transaction_PS
AS PARTITION Transaction_PF
TO (FG_0,FG_1,FG_2,FG_3,FG_4,FG_5,FG_6,FG_7,FG_8,FG_9, FG_123, FG_456, FG_789, PRIMARY);
The PRIMARY filegroup should be empty, there shouldn't be any rows unless somehow a NULL showed up in the PartitionColumn. Now comes the tricky part. I want to move part number 3275 to its own partition. It's currently in FG_5 right now (3275 modulo 10). New filegroup is called FG_3275, simple enough. Modifying the partition function is easy enough:
ALTER PARTITION FUNCTION Transaction_PF()
SPLIT RANGE (1003);
and the partition function now looks like this:
0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 1000, 1001, 1002, 1003
Here's the part I don't get. The only way I can modify the partition scheme is to do something like:
ALTER PARTITION SCHEME Transaction_PS
NEXT USED FG_3275;
But now the scheme looks like this:
FG_0,FG_1,FG_2,FG_3,FG_4,FG_5,FG_6,FG_7,FG_8,FG_9, FG_123, FG_456, FG_789, PRIMARY, FG_3275
But I want it to look like:
FG_0,FG_1,FG_2,FG_3,FG_4,FG_5,FG_6,FG_7,FG_8,FG_9, FG_123, FG_456, FG_789, FG_3275, PRIMARY
What am I missing here? There's got to be an easy solution for this. Right now, I'm still in development so I'm open to any new design suggestions.
Set NEXT USED *before* doing the SPLIT.
Eddie Wuerch
MCM: SQL
October 4, 2019 at 6:56 pm
are you using a sliding partition strategy for archiving?
don't look at the file groups. file groups is how it is stored, and potentially archived. that is independent from the logical ordering of the items in the partition
look at the partition numbers. they will always be in the "expected" order you want to visualize.
the file group are going to be used in a round robin fashion, so
might be in order on creation, but as you archive partitions(each month?) the file groups will reuse the old filegroup in a loop;
you should see them do something like this:
at start: FG_0,FG_1,FG_2,FG_3,FG_4,FG_5
first Archive: FG_0,FG_2,FG_3,FG_4,FG_5,FG_1
next: FG_0,FG_3,FG_4,FG_5,FG_1,FG_2,
next: FG_0,FG_4,FG_5,FG_1,FG_2,FG_3,
next: FG_0,FG_5,FG_1,FG_2,FG_3,FG_4,
etc: FG_0,FG_1,FG_2,FG_3,FG_4,FG_5,
Lowell
October 4, 2019 at 7:06 pm
in this case, not using partitions for archiving purposes at all.
What I am using it for is for physical file allocation purposes - we want to isolate certain huge part numbers into its own files on faster disks. So eventually, I would see this for the scheme:
FG_0....FG_9, FG_123, FG_456, FG_789, PRIMARY
FG_0....FG_9, FG_123, FG_456, FG_789, FG_3275, PRIMARY
FG_0....FG_9, FG_123, FG_456, FG_789, FG_3275, FG_6666, PRIMARY
etc. So as time goes on, we want to physically move big parts into its own file group.
What I guess I can do is to pre-allocate a bunch of partitions, and then associate them later with pre-allocated generic filegroup names. That probably will be ok but still leaves open the question as to how to add new ones. For example, I could do:
Partition function 0,1,2,3,4,5,6,7,8,9,1000, 1001, 1002, 1003, 1004, 1005, 1006, 1007, 1008, 1009
Partition scheme FG_0.....FG_9, FG_1000, FG_1001....FG_1009, PRIMARY
October 4, 2019 at 7:37 pm
> Set NEXT USED *before* doing the SPLIT.
AH! That was the secret sauce. Thanks!!!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply