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
SELECT * FROM sys.dm_db_partition_stats WHERE OBJECT_ID = Object_ID(‘DatabaseLog’)
GO
/*create a staging table with DatabaseLog_Staging table
note that Identity in different in destination table orginal table has increment of 1 and new table had increment of 5
*/
CREATE TABLE [dbo].[DatabaseLog_Staging](
[DatabaseLogID] [int] IDENTITY(1,5) NOT NULL,
[PostTime] [datetime] NOT NULL,
[DatabaseUser] [sysname] NOT NULL,
[Event] [sysname] NOT NULL,
[Schema] [sysname] NULL,
[Object] [sysname] NULL,
[TSQL] [nvarchar](max) NOT NULL,
[XmlEvent] [xml] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
–Zero record
SELECT COUNT(1) FROM [dbo].[DatabaseLog_Staging]
GO
ALTER TABLE [DatabaseLog] SWITCH TO [DatabaseLog_Staging] ;
GO
–check count of records in [DatabaseLog_Staging] table after switch (Record Count 112)
SELECT COUNT(1) FROM [dbo].[DatabaseLog_Staging]
GO
–shows zero record
SELECT COUNT(1) FROM [dbo].[DatabaseLog]
GO
–rolling back switch operation
ALTER TABLE [DatabaseLog_Staging] SWITCH TO [DatabaseLog] ;
GO
We can think of other use case where switch can be helpful but I’ll leave it up to your imaginations.
adrshen 