Technical: Microsoft – SQL Server – Partitioning – Table – Swap in & Out – Error – ALTER TABLE SWITCH statement failed. There is no identical index in source table ” for the index ” in target table ”

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

Output:

IndexColumns

Explanation:

  • 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

Possible Fix:

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

Errors

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

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'.

References:

System Tables

DDL

 

Web Resource

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s