J. Bloggs J. Bloggs - 1 year ago 66
SQL Question

What happens to indexes when using ALTER SCHEMA to transfer a table?

I have a massive job that runs nightly, and to have a smaller impact on the DB it runs on a table in a different schema (EmptySchema) that isn't in general use, and is then swapped out to the usual location (UsualSchema) using

ALTER SCHEMA TempSchema TRANSFER UsualSchema.BigTable
ALTER SCHEMA UsualSchema TRANSFER EmptySchema.BigTable
ALTER SCHEMA EmptySchema TRANSFER TempSchema.BigTable

Which effectively swaps the two tables.

However, I then need to set up indexes on the UsualSchema table. Can I do this by disabling them on the UsualSchema table and then re-enabling them once the swap has taken place? Or do I have to create them each time on the swapped out table? Or have duplicate indexes in both places and disable/enable them as necessary (leading to duplicates in source control, so not ideal)? Is there a better way of doing it?

There's one clustered index and five non-clustered indexes.


Answer Source

Indexes, including those that support constraints, are transferred by ALTER SCHEMA, so you can have them in both the source and target object schema.

Constraint names are schema-scoped based on the table schema and other indexes names are scoped by the table/view itself. It is therefore possible to have identical index names in the same schema but on different tables. Constraint names must be unique within the schema.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download