Table partitioning on an existing table and using different filegroups

Question

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;
go

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

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

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


-- Drop the current default index, we want to build one later on the partition key
ALTER TABLE [dbo].[Comments] DROP CONSTRAINT [PK_Comments_Id] WITH ( ONLINE = OFF )

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

go
-- 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
        END
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;

Check the rows for partition 1

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

Check the rows for partition 2

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

Check the file sizes

(very large query)

-- Create new clustered index to correctly distribute the data

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

Check the file sizes again

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?

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])