I currently have a rather large table (5-7 million rows). This table is rebuild regularly by a procedure which builds the data in a staging table, then switches the data into the production table using the
ALTER TABLE .. SWITCH TO .. statement.
BEGIN TRAN; -- Rebuild indexes ALTER INDEX IX_NC_GroupEvent_staging_GroupName on [dbo].[GroupEvent_staging] REBUILD; ALTER INDEX IX_NC_GroupEvent_staging_Created ON [dbo].[GroupEvent_staging] REBUILD; -- Empty production table TRUNCATE TABLE [dbo].[GroupEvent]; -- Switch data from staging-table into production table ALTER TABLE [dbo].[GroupEvent_staging] SWITCH TO [dbo].[GroupEvent] COMMIT;
When this operation is performed, does the current state of the indexes (or index data if you will) get switched as well? I am asking because of 2 reasons:
1) In order to perform a
SWITCH TO statement, a requirement is that both the source and target table must contain identical indexes. This leads me to believe the index-data might be switched as well, but I don't know how to verify this.
2) The main benefit of building the table in this manner is to avoid performing excessive work on the production table while in use. Naturally, it would make me very happy if I were able to rebuild indexes on the staging-table and have the rebuilt indexes being switched over to the production indexes along with the table.