I am migrating an old database from SQL Server 2005 to SQL Server 2014. This database has a partitioned table; a scheduled job periodically moves old data from the "online" table to the the historical table.
The job fails when I try to move the partition with the following code:
ALTER TABLE Warehouse.TBW_MY_DATA SWITCH PARTITION 1 TO Warehouse.TBW_MY_DATA_STAGING
Here is the error message:
Msg 4939, Level 16, State 1, Line 4 ALTER TABLE SWITCH statement failed. index 'MY_DATABASE.Warehouse.TBW_MY_DATA_STAGING.PKW_MY_DATA_STAGING' is in filegroup 'FG_EDRN_2009123100085' and partition 1 of index 'MY_DATABASE.Warehouse.TBW_MY_DATA.PKW_MY_DATA' is in filegroup 'FG_EDRN_2007033100002'.
Actual screenshot of the error:
MY_DATABASE.Warehouse.TBW_MY_DATA.PKW_MY_DATA are the clustered indexes of the primary keys.
What I tried:
To isolate the problem I run the job with different compatibility levels.
The error only occurs using compatibility level 120 (SQL Server 2014). Everything works fine with compatibility level 100 and 110 (respectively SQL Server 2008 and SQL Server 2012). So probably something changed in partition handling from SQL Server 2012 to SQL Server 2014.
I read the article about "Transferring Data Efficiently by Using Partition Switching" on TechNet, but no deprecation date/version is ever mentioned even if I noticed that the article is available only for SQL Server 2005 and 2008.