“Switch” Huge Non Partitioned Tables in Seconds

ALTER TABLE [onetable] SWITCH TO [anothertable]

There are many challenges with gigantic tables and modifying their schema is one of those that could make many operational DBAs cry. I had to go through a schema change where I have to make data type and identity changes to one of the largest transactional table in the database with 5.8B rows and 1.4TB in size.

Changing identity increment is something that most likely will lead many DBAs to create another table (with pretty much same schema) and then insert records from source to destination table and then rename destination to its original table name. Using INSERT would involve the standard mechanism of SQL Server, where each DML operation will have to log in transnational log.

Moving forward with SQL Server 2008, Microsoft enable DBAs to switch partition the common concept is that this SWITCH operation is limited to only partition tables. However, documentation also explain that we can also witch a non-partitioned table to another non-partitioned empty table with same schema. There are restriction and limitation when using this but here is simple example how to make it happen.

USE [AdventureWorksDW2012]

GO

–check count of records in DatabaseLog table (Record Count 112)

SELECT COUNT(1) FROM [dbo].[DatabaseLog]

GO

–find out the partition number of table DatabaseLog. For large table it is good change you will see multiple partitionids belongs to one partition number