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.

 

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s