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’)
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.