locked
Switch out data from partition table to non-partition table RRS feed

  • Question

  • Hi Everyone,

    We have a partition table in SQL SERVER 2014 and we want to switch out a partition of this table to a temporary table on the same file group. I read lots of article, that we should have the same non-clustered on temporary table.

    But when I switched out the data without creating those non-clustered indexes  I did not face any problem.So Can we say that creating non-clustered index on temporary table is not required and we just need to create the clustered index on staging table?

    Thanks


    A.G

    Tuesday, July 18, 2017 6:28 PM

Answers

  • There will be little or no effect on SWITCH performance since only space allocations are affected. Of course, you won't be able to SWITCH back into the source table until you create all the aligned non-clustered indexes. Whether or not this is a problem depends on your use case for SWITCH.

    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Wednesday, July 19, 2017 11:51 AM
    Answerer

All replies

  • Create a clustered index on the file group instead of the partition scheme to replace the existing index for the partition.

    A Fan of SSIS, SSRS and SSAS

    Tuesday, July 18, 2017 7:28 PM
  • But when I switched out the data without creating those non-clustered indexes  I did not face any problem.So Can we say that creating non-clustered index on temporary table is not required and we just need to create the clustered index on staging table?

    Although not commonly done, the target of the SWITCH does not need to have all the non-clustered indexes as the a source. Corresponding indexes that exist in both are switched and indexes that exist only in the source are emptied during SWITCH. Those indexes that exist in both source and target must be identical and reside on the same filegroups.

    You cannot switch into a target that has more non-clustered indexes than the source.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Tuesday, July 18, 2017 8:35 PM
    Answerer
  • Hi Dan,

    Thanks for your reply.

    Just to confirm, if I don't create all non-clustered index on destination table, will not effect on the performance of switch statement or cause any other problem?

    Thanks

    Aspet


    A.G

    Tuesday, July 18, 2017 9:15 PM
  • There will be little or no effect on SWITCH performance since only space allocations are affected. Of course, you won't be able to SWITCH back into the source table until you create all the aligned non-clustered indexes. Whether or not this is a problem depends on your use case for SWITCH.

    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Wednesday, July 19, 2017 11:51 AM
    Answerer