Have a sliding partition where I need to remove the trailing partition when they expire. I create an empty table that matches the existing table. When I create the empty table, I specify the file group that I am going to be removing. I also create all the indexes on the empty table so it matches exactly the data table on that partition.
Once the empty table and indexes are created, I issue the command
alter table mydatatable switch partition 2 to emptytable
The command takes forever to run and after reading everything I could about the switching, I thought it was only moving meta data over and a very quick step.
My plan is to swap the data to the empty one and then truncate the empty table and remove the file group.
To add a little more details. The data table started off as a non-partitioned table. Due to the number of r5ecords in it, we had to partition it. So I created the partition schema, function and all of the file groups. I dropped the clustered index and all other indexes and recreated them all. The partition process looks to be successful.
When I run the alter table command, it feels like there is data being transferred and not just meta data.