8

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.

Example:

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.

6

does the index data get switched as well?

Yes. It would be bizarre if it didn't because then queries would return the wrong results or we would have to manually rebuild the indexes after switching.

I don't know how to verify this

One way would be to just try it

CREATE TABLE [dbo].[GroupEvent]
  (
     GroupName VARCHAR(100) INDEX IX_NC_GroupEvent_staging_GroupName,
     Created   DATETIME INDEX IX_NC_GroupEvent_staging_Created 
  );

CREATE TABLE [dbo].[GroupEvent_staging]
  (
     GroupName VARCHAR(100) INDEX IX_NC_GroupEvent_staging_GroupName,
     Created   DATETIME INDEX IX_NC_GroupEvent_staging_Created 
  );

INSERT INTO [dbo].[GroupEvent_staging]
VALUES      ('Group1',GETDATE()),
            ('Group2',GETDATE());

ALTER INDEX IX_NC_GroupEvent_staging_GroupName ON [dbo].[GroupEvent_staging] REBUILD;

ALTER INDEX IX_NC_GroupEvent_staging_Created ON [dbo].[GroupEvent_staging] REBUILD;

SELECT index_id,
       allocated_page_file_id,
       allocated_page_page_id
FROM   sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('[dbo].[GroupEvent_staging]'), NULL, NULL, 'DETAILED')
WHERE  is_allocated = 1; 

-- Empty production table
TRUNCATE TABLE [dbo].[GroupEvent];

-- Switch data from staging-table into production table
ALTER TABLE [dbo].[GroupEvent_staging] SWITCH TO [dbo].[GroupEvent];

SELECT index_id,
       allocated_page_file_id,
       allocated_page_page_id
FROM   sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('[dbo].[GroupEvent]'), NULL, NULL, 'DETAILED')
WHERE  is_allocated = 1; 

SELECT GroupName
FROM   [dbo].[GroupEvent];

DROP TABLE [dbo].[GroupEvent], [dbo].[GroupEvent_staging]; 

The only object accessed in the process of returning the two rows was the index showing that the data must have been switched.

enter image description here

The above also compares the result from sys.dm_db_database_page_allocations for GroupEvent_Staging before the switch with a similar query for GroupEvent after the switch to see that the pages remain the same for both the heap itself (index_id=0) and the two nonclustered indexes (ids 2 & 3). This shows the switch was metadata only with ownership of the allocated pages transferred.

enter image description here

  • Thanks for not only answering the question, but also explaining how you came to the conclusion. Great! – krystah Jun 20 '16 at 7:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.