Partition Switching for Big Data Loads – Partition Switch Out

This is the last in a four part series of blog post explaining Partition Switching using SQL Server 2014.

Before switching data into a specific partition we must first ensure the relevant partition is empty, otherwise the Partition Switch In operation will fail. The Partition Switch Out command will work perfectly in removing the data from the destination table into a separate staging table.

A staging table with exactly the same structure as the destination Fact table must exist for this operation. The data will be moved from the Fact table to the Staging table.

As in the previous posts we used monthly filegroups where the Staging Fact table should be on the correct filegroup before the Switch Out command is executed. The following code snippets will perform the Partition Switch Out. You can amend the code to fit into your design and structure as you please.

Firstly remove the columnstore index

  • If Exists(Select * From Sys.Indexes Where OBJECT_ID = Object_Id (‘dbo.Staging_Fact_Out’) And NAME =’CCI_Staging_Fact_Out’)Drop Index [CCI_Staging_Fact_Out] On [dbo].[Staging_Fact_Out] WITH ( ONLINE = OFF )

Then create the clustered columnstore index on the correct filegroup

  • Declare @FileGroupMonth varchar(40) = ‘Month_’ + Convert(Varchar,DatePart(Month,@LoadDate))
  • Declare @Sql varchar(1000) = ‘Create Clustered Columnstore Index [CCI_Staging_Fact_Out] On [dbo].[Staging_Fact_Out] With (Drop_Existing = Off) On ‘ + @FileGroupMonth
  • If Not Exists(Select * From Sys.Indexes Where OBJECT_ID = Object_Id(‘dbo.Staging_Fact_Out’) And Name =’CCI_Staging_Fact_Out’)
    Exec (@Sql)

Retrieve the specific partition number

  • Declare @PartitionNumber Int = (Select Top 1 $partition.pfFunction(@Date) From [dbo].[Fact_Table])

Finally do the partition switch out 🙂

  • Alter Table Fact_Table
    Switch Partition @PartitionNumber To Staging_Fact_Out

Now you can perform the Partition Switch In explained in the previous article because the relevant partition will be empty.

This entry was posted in SQL Server 2008, SQL Server 2012, SQL Server 2014 and tagged , , . Bookmark the permalink.

Leave a Reply

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

You are commenting using your 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