Partitioning existing table doesn't work, why?

Question

I need help with portioning existing table in the Database. I'm using MSSQL server 2017 and I have table called ACD stores data about calls received and it contains hundred of thousands of records and it is expected to have millions later by the end of the year. I'm trying to partition the table based on StartDateTime column which has DateTime data type in which I will have 12 files for each month but that doesn't work. below is the steps I followed but with no luck:

1. I created 12 file groups and named each one with month name(Jan, Feb,..., Dec)(below is examples):

   ALTER DATABASE IPCCDB ADD FileGroup January
   
   Go
   
   ALTER DATABASE IPCCDB ADD FileGroup Febuary
   
   Go
   

2. I added data file to each file group.

   ALTER DATABASE [IPCCDB]
   
   ADD FILE
   
   
   
   (
   
   NAME = [JanCalls],
   
   FILENAME = 'D:\SQLServer2017Media\MSSQLSERVER\MSSQL14.MSSQLSERVER\MSSQL\DATA\IPCCDB.1ndf',
   
   SIZE = 3072 KB,
   
   
   
   MAXSIZE = UNLIMITED,
   
   
   
   FILEGROWTH = 1024 KB
   
   ) TO FILEGROUP [January];
   

3. I created partition function to map the rows of a partitioned table(ACD table) into partitions based on the values of a partitioning column(StartDateTime).

   CREATE PARTITION FUNCTION [PartitionCallsgByMonth] (datetime)
   
   AS RANGE RIGHT FOR VALUES ('20220201', '20220301', '20220401','20220501', '20220601', '20220701', '20220801', '20220901', '20221001', '20221101', '20221201');
   

4. I created partition scheme to map the partitions of a partitioned table to filegroups.

   CREATE PARTITION SCHEME MyPartitionScheme AS PARTITION [PartitionCallsgByMonth]
   TO (January, February, March, April, May, June, July, August, September, October, November, December);
   

5. I loaded the records in the table into temp table.
6. I truncated ACD table and drop the clustered index IX_ACD.
7. I loaded the data again to ACD table with the INSERT INTO SELECT FROM statement. Eventually when I check how many records inserted to each data file in each file group i see that all the data was inserted to the first data file only.

PartitionNumber PartitionFilegroup  NumberOfRows
1      January    767681

below is sample record from ACD table

SessionID   SessionSeqNum   ResourceID  RingTime    TalkTime    HoldTime    CSQID   StartDateTime   EndDateTime
325532  0   1033    12  474 0   5   2022-01-22 15:12:32 2022-01-22 15:20:38

Answer 1

I truncated ACD table and drop the clustered index IX_ACD.

After that you need to recreate the clustered index on the target partition scheme. It looks like you created the clustered index on the January filegroup instead of on MyPartitionScheme.