Technical: Microsoft – SQL Server – Partitioning – Table – Swap in & Out – Error – ALTER TABLE SWITCH statement failed. There is no identical index in source table ‘<Source-Table>’ for the index ‘<Index>’ in target table ‘<partition-table>’
SQL – Partition Switch Statement
When we issue a statement such as :
TRUNCATE TABLE [DataStaging].[Sales]; ALTER TABLE dbo.[Sales] SWITCH PARTITION 3 TO [DataStaging].[Sales];
SQL – Error Statement
We get an error similar to the one below:
ErrorNumber : 4947 ErrorSeverity : 16 ErrorState : 1 ErrorSeverity : 16 ErrorState : 1 ErrorProcedure : ErrorLine : -1 ErrorMessage : ALTER TABLE SWITCH statement failed. There is no identical index in source table 'DBSales.dbo.Factor' for the index '_dta_index_Factor_c_5_1234103437__K6_K5_K3_K4' in target table 'DBSales.DataStaging.Factor' .
Review Index Creation Script
Spent a lot of time to trying to get this one to work!
Scripted out the Index Creation Script for the base table:
/****** Object: Index [_dta_index_Factor_c_5_1234103437__K6_K5_K3_K4] */ /* Script Date: 07/23/2013 16:32:11 ******/ CREATE CLUSTERED INDEX [_dta_index_Factor_c_5_1234103437__K6_K5_K3_K4] ON [dbo].[Factor] ( [SSN] ASC, [FirstName] ASC, [LastName] ASC, [ZipCode] ASC ) WITH ( PAD_INDEX = ON , STATISTICS_NORECOMPUTE = OFF , SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , DROP_EXISTING = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON , FILLFACTOR = 80 ) GO
Scripted out the index for the Staging Table and it matched.
Review Index Meta-Data
The SQL Script below tabulates the Index Columns:
declare @objectIDTemplate int declare @objectIDTemplateX int declare @IndexId tinyint set @objectIDTemplate = OBJECT_ID('dbo.Factor') set @objectIDTemplateX = OBJECT_ID('DataStaging.Factor') set @IndexId = 1 select SCHEMA_NAME(tblObject.schema_id) as schemaName , object_name(tblIndexColumn.object_id) as objectName , tblColumn.name , tblIndexColumn.is_included_column , tblIndexColumn.is_descending_key , tblIndexColumn.key_ordinal , tblIndexColumn.partition_ordinal from sys.index_columns tblIndexColumn inner join sys.columns tblColumn ON tblColumn.object_id = tblIndexColumn.object_id and tblColumn.column_id = tblIndexColumn.column_id inner join sys.objects tblObject ON tblColumn.object_id = tblObject.object_id inner join sys.indexes tblIndex ON tblIndexColumn.object_id = tblIndex.object_id AND tblIndexColumn.index_id = tblIndex.index_id where tblIndexColumn.index_id = @IndexId and tblIndexColumn.object_id in ( @objectIDTemplate, @objectIDTemplateX) and tblIndexColumn.is_included_column = 0 order by SCHEMA_NAME(tblObject.schema_id) desc , object_name(tblIndexColumn.object_id) , tblIndexColumn.key_ordinal
- A quick review of the listing above shows that the main table has 5 columns; while our staging table has 4 columns
- The first column is for our partitioning key; even though that column (Factor_ID) is not part of our initial\submitted index statement, SQL Server added that column has partitioning will benefit from it
- SQL Server uses the sys.index_columns DMV, specifically column partition_ordinal to indicate that this column is part of the Partitioning columns
- Note that in cases where partition_ordinal is set and not 0, we can not assume that it was not explicitly added as part of the Index Design; That is, this column by itself does not fully delineate design or SQL Server Sourcing
- Please keep this silent SQL Help in mind when designing indexes for Partitioned tables; For Non-Clustered Indexes the partitioned columns can be part of the main index creation or part of the Included Columns
- In the case of Clustered Indexes, the partitioned columns have to be member-ed in the main Index Creation
If the base table will be involved in a Partitioning Arrangement, then find a way to include the partitioning columns in the definition of the Index; this needs to be effected on both the base and partition tables:
/****** Object: Index [_dta_index_Factor_c_5_1234103437__K6_K5_K3_K4] */ /* Script Date: 07/23/2013 16:32:11 ******/ CREATE CLUSTERED INDEX [_dta_index_Factor_c_5_1234103437__K6_K5_K3_K4] ON [dbo].[Factor] ( [SSN] ASC, [FirstName] ASC, [LastName] ASC, [ZipCode] ASC, [FactorID] ASC, ) WITH ( PAD_INDEX = ON , STATISTICS_NORECOMPUTE = OFF , SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , DROP_EXISTING = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON , FILLFACTOR = 80 ) GO
As a placeholder, other errors are briefly mentioned below:
Errors – Missing Pair
Note that if the Index is otherwise missing, you will get a more precise error stating “does not have”.
Msg 4913, Level 16, State 1, Line 3 ALTER TABLE SWITCH statement failed. The table 'dbo.Factor' has clustered index '_dta_index_Factor_c_5_1234103437__K6_K5_K3_K4' while the table 'DataStaging.Factor' does not have clustered index.
Other Errors – Mismatched Column Names
Msg 4942, Level 16, State 1, Line 4 ALTER TABLE SWITCH statement failed because column 'FactorBase_ID' at ordinal 1 in table 'DBSales.dbo.FactorBase' has a different name than the column 'Factor_ID' at the same ordinal in table 'DBSales.Staging.FactorBase'.
Other Errors – Foreign Key Dependencies
Dependent tables are also problematic and we will to cover in a later post.
Msg 4967, Level 16, State 1 ALTER TABLE SWITCH statement failed. SWITCH is not allowedbecause source table 'dbo.Factor' contains primary key forconstraint 'fk_Culture_Factor'.
- Alter Table Switch fails with 4947 even when all the publicly exposed metadata for the indexes in source and target tables perfectly match
- No Identical Index Error When Switching Data Partition