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:
enter image description here
MY_DATABASE.Warehouse.TBW_MY_DATA_STAGING.PKW_MY_DATA_STAGING and 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.
I checked the list of deprecated features and the list of breaking changes in SQL Server 2014, but I found nothing concerning partition switching.
1 Answer 1
Actually, I think this is the opposite of a bug. the 2008 document states "Source and target tables must share the same filegroup" but it looks as though they only started paying attention to their rules in 2014. This article (made for 2014 / 2016 CTE) states "The corresponding indexes, or index partitions, must also reside in the same filegroup" TechNet