IndexStaging Script Only Fails With CreateStagingClusteredIndex


When you create a stage table using CreateStagingClusteredIndex and try to execute the IndexStaging from the script, you get an error for identical index exists.

Msg 1779, Level 16, State 0, Line 1
Table 't1' already has a primary key defined on it.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
Msg 4947, Level 16, State 1, Line 10
ALTER TABLE SWITCH statement failed. There is no identical index in source table 'PartitionTest.dbo.t1' for the index 'NC_SOH_CUST' in target table 'PartitionTest.dbo.SalesOrderHeader' .
Steps to reproduce:
  1. Create staging table - "CreateStagingClusteredIndex"
  2. Create Script for Index Staging - "IndexStaging" with "O:o" argument
  3. Execute Index Staging Script
  4. Receive Error for duplicate index
Attached is a modified/simplified version of the PartitionMgmt_2012.sql file supplied in the PartitionManagement3.0.zip file.

file attachments


MrPerson wrote Sep 23, 2013 at 8:42 PM

FYI - the IndexStage works as expected if you execute the command. My use case is that I want to script the indexes during the data load to remove the scripting time for the indexes.


wrote Sep 23, 2013 at 9:08 PM