• andrea4618 - Tuesday, December 5, 2017 11:25 AM

    A couple of honest questions:
    1.  What happens to indexes, triggers, and other dependent objects  with schema swapping?  You may not want an index on the table in the shadow schema, but you probably will want that index in the active reporting schema.  Does the index in the dbo schema need to be rebuilt when the updated table is swapped in?
    2.  Is there any benefit to setting this up in a SSIS package rather than doing the schema swap as a Transact-SQL job step?
    3.  Technet specifies that "All permissions associated with the securable that is being transferred are dropped when it is moved."  So it sounds like you would have to set up steps in your ETL to restore any object specific permissions?

    If it is anything like a partition swap you need to have the indexes.  Other things you would have to test.  I found with partition swapping I didn't need the default constraints, not sure about indexes as the ones on the tables I was working with we dropped the indexes before to the work to complete the data cut on the database. DRI may need to be there, but the two tables in this particular database with foreign keys weren't affected.