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?