I have an existing table on the primary filegroup, which I want to partition. The partition key is on year, which is a calculated column. I want to partition the table in such a way that in the end the data for each year will be on it's own filegroup. I first want to split of 2 years, so I can later test some more on how to split the other data off with the split command. Now, I can create the partition function and scheme and I also see that the data for a certain year is in the correct the partition, but I cannot get the physical data in the correct file groups. It seems as if the data still resides in that primary file group. I tried rebuilding the index, but that still does not move the data in the correct file group. In the end the table will have a clustered columnstore index, but I also tried with a clustered rowstore index. Reason I did this is because SQL Server does not seem to allow a columnstore index to split and merge non-empty partitions (i tried some stuff with split and merge but same result), so I thought that that would at least work. Please fill me in on this if you have any suggestions or comments. Iam using SQL Server 2019 by the way.

Now for the code, I use the Stackoverflow2013 database:

use StackOverflow2013;

-- Create file groups for partitions
alter database [StackOverflow2013]
add filegroup StackOverflow2013_2008;

ALTER DATABASE [StackOverflow2013]
    NAME = [StackOverflow2013_2008],
    FILENAME = 'E:\DATA\StackOverflow2013_2008.ndf',
        SIZE = 1024 KB, 
        FILEGROWTH = 512 MB
    ) TO FILEGROUP [StackOverflow2013_2008]
alter database [StackOverflow2013]
add filegroup StackOverflow2013_2009;

ALTER DATABASE [StackOverflow2013]
    NAME = [StackOverflow2013_2009],
    FILENAME = 'E:\DATA\StackOverflow2013_2009.ndf',
        SIZE = 1024 KB, 
        FILEGROWTH = 512 MB
    ) TO FILEGROUP [StackOverflow2013_2009]

-- Drop the current default index, we want to build one later on the partition key

-- Add partition key column
alter table [StackOverflow2013].[dbo].[Comments]
add [year] as (datepart(year, CreationDate));

-- Add partition function based on year 
-- For now we only want 2008 and 2009, other years will be migrated later to test with split function
create partition function fun_Comments(int)
as range left for values (2008, 2009);

-- Add partition scheme
create partition scheme scheme_Comments
as partition fun_Comments
to (StackOverflow2013_2008, StackOverflow2013_2009, [Primary]);

-- Check the partition numbers and who's next
SELECT DestinationId = DestinationDataSpaces.destination_id
    ,FilegroupName = Filegroups.name
    ,PartitionHighBoundaryValue = PartitionRangeValues.value
    ,IsNextUsed = CASE 
        WHEN DestinationDataSpaces.destination_id > 1
            AND LAG(PartitionRangeValues.value, 1) OVER (
                ORDER BY DestinationDataSpaces.destination_id ASC
                ) IS NULL
            THEN 1
        ELSE 0
FROM sys.partition_schemes AS PartitionSchemes
INNER JOIN sys.destination_data_spaces AS DestinationDataSpaces ON PartitionSchemes.data_space_id = DestinationDataSpaces.partition_scheme_id
INNER JOIN sys.filegroups AS Filegroups ON DestinationDataSpaces.data_space_id = Filegroups.data_space_id
LEFT OUTER JOIN sys.partition_range_values AS PartitionRangeValues ON PartitionSchemes.function_id = PartitionRangeValues.function_id
    AND DestinationDataSpaces.destination_id = PartitionRangeValues.boundary_id
WHERE PartitionSchemes.name = N'scheme_Comments'
ORDER BY DestinationId ASC;

enter image description here

Check the rows for partition 1

SELECT * FROM Comments 
WHERE $PARTITION.fun_Comments(year) = 1; 

enter image description here

Check the rows for partition 2

SELECT * FROM Comments 
WHERE $PARTITION.fun_Comments(year) = 2; 

enter image description here

Check the file sizes

(very large query)

enter image description here

-- Create new clustered index to correctly distribute the data

create clustered index [CCIX_Comments] ON [dbo].[Comments] (year)

Check the file sizes again

enter image description here

So it seems to me that all the data is actually still in the primary filegroup, because the new filegroups are emtpy. The table is 7 GB, so I would at least expect some data to be in there.

So basically my question is, how can I correctly redistribute the data over the files in the filegroups in this scenario?

  • 1
    Your clustered index create statement isn’t aligned to the partitioning scheme, which means it’s all going to the primary filegroup. There are examples in this old post of mine about how to do and fix that: brentozar.com/archive/2018/10/… Dec 24, 2021 at 8:59

1 Answer 1


your create index statement needs the ON clause to use the partition scheme you've created:

create clustered index [CCIX_Comments] ON [dbo].[Comments]
( [year] ASC )
ON [scheme_Comments]([year])
  • Thanks a bunch guys! I definitely overlooked this and will never forget this one... :-) Works fine now! Dec 26, 2021 at 14:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.