• The exact list of requirement for partition switch seems very long:

    http://msdn.Microsoft.com/en-us/library/ms191160(v=sql.105).aspx

    That is exactly one reason I am trying to come up with this sp to hide/deal with these details.

    I did few tests and it seems to me that we don't have to have FK to be able to truncate the partition (switch to an empty clone table).

    I really hope one day sql server will support partition truncation natively.

    As for the index requirement, I did some quick test and you are right: all other indexes have to be aligned. Otherwise we will get an error such as

    'ALTER TABLE SWITCH' statement failed. The table 'TableName' is partitioned while index 'IX_TableName_IndexName' is not partitioned.

    I want to confirm that if we have other aligned index(es) on the original table, we don't have to create them in the empty clone table and the partition swith will work.