This is the third in a series of blogs about partition switching for SQL Server 2014. This blog will focus on the actual Alter Table … Switch statement spoken about in the first blog.
A number of steps has to be followed in order to perform the Alter Table … Switch statement. The Staging Fact table should be on the same File Group as the Fact table and the structure and indexes of the tables must match. A default constraint should also be added to the Staging Fact table because the Switch statements validates the data in the Staging table using this constraint.
In our solution both the Staging_Fact and Fact table have a clustered column store index. Before inserting data into the Staging_Fact the table is dropped and created in the correct File Group and the Column Store index created. We decided to have one File Group per month. The following code snippets is to drop and create the table on the correct File Group
- Declare @FileGroup varchar(100) = ‘FileGroupName’ + Convert(varchar,Convert(int,substring(convert(varchar(8),@Date),5,2)))
Declare @SQL varchar(max) = ”
If Exists(Select name From Sys.tables Where OBJECT_ID = Object_Id(‘dbo.Staging_Fact’))
Drop TABLE dbo.Staging_Fact;Set @Sql = ‘CREATE TABLE [dbo].[Staging_Fact]([Column1] [int] NULL,
[Date] [int] NOT NULL) On ‘ + @FileGroup
If Not Exist (Select i.name From sys.indexes i
JOIN sys.tables t
ON (i.type_desc = ‘CLUSTERED COLUMNSTORE’)
WHERE t.name = ‘Staging_Fact’
AND i.name = ‘CCI_Staging_Fact’)
Create Clustered Columnstore Index CCI_Staging_Fact
Now we can do the code to do the partition switch into the Fact table. The following series of code snippets will perform the switch in.
Retrieve the specific partitition number
- Declare @PartitionNumber Int = (Select Top 1 $partition.pfFunction(@Date) From [dbo].[Fact_Table])
Drop the constraint if it already exist on the Staging_Fact table
- If Exists (Select * From Information_Schema.Table_CONSTRAINTS
AND TABLE_NAME = ‘Staging_Fact’ AND CONSTRAINT_NAME = ‘CK_Staging_Fact’)
Exec(‘Alter Table [dbo].[Staging_Fact] Drop Constraint [CK_Staging_Fact]’)
Create the constraint on the Staging_Fact table
- Declare @SQL varchar(2000) = ”
Set @SQL = ”Set @SQL = ‘Alter Table [dbo].[Staging_Fact] With Check Add Constraint [CK_Staging_Fact] Check ([Date] =’ + Convert(varchar,@Date) + ‘)’ ; Exec (@SQL)
And then, the Alter Table … Switch you have been reading about 🙂 The next statement will switch the data to the Fact table on the specified partition
- Alter Table [dbo].[Staging_Fact]
Switch To [dbo].[Fact_Table] Partition @PartitionNumber
A partition switch out should also be done if data already exist on the partition you want to insert data into. The switch out will remove the data from the specified partition. The last blog in this series will explain this in more detail.