Basic Partition Switching Question

Posted on

Question :

I’m new to partition switching and have used this basic syntax previously:

TRUNCATE TABLE TARGET

ALTER TABLE SOURCE SWITCH TO TARGET

I just came across an SSIS package where there is this syntax within a loop:

TRUNCATE TABLE TARGET

ALTER TABLE SOURCE
SWITCH PARTITION $PARTITION.MONTHFN(@MONTH) TO TARGET PARTITION $PARTITION.MONTHFN(@MONTH)

If all the partitions are being switched to the target table, is it necessary to do the switching by partition? Is this only because the source is already partitioned?

Answer :

This syntax for ALTER TABLE SWITCH requires you to specify a single partition.

ALTER TABLE [ database_name . [schema_name ] . | schema_name. ] source_table_name   
{  
    ALTER COLUMN column_name  
        {   
            type_name [ ( precision [ , scale ] ) ]   
            [ COLLATE Windows_collation_name ]   
            [ NULL | NOT NULL ]   
        }  
    | ADD { <column_definition> | <column_constraint> FOR column_name} [ ,...n ]  
    | DROP { COLUMN column_name | [CONSTRAINT] constraint_name } [ ,...n ]  
    | REBUILD {  
            [ PARTITION = ALL [ WITH ( <rebuild_option> ) ] ] 
          | [ PARTITION = partition_number [ WITH ( <single_partition_rebuild_option> ] ]
      } 
    | { SPLIT | MERGE } RANGE (boundary_value)  
    | SWITCH [ PARTITION source_partition_number  
        TO target_table_name [ PARTITION target_partition_number ]  
}  
[;]  

I found several articles that show different ways to automate the switching of all partitions – this is just one example

DECLARE @Partitions TABLE (PartitionId int PRIMARY KEY CLUSTERED);
DECLARE @PartitionId INT;

INSERT @Partitions(PartitionId)
    SELECT 
        prv.boundary_id PartitionId
    FROM sys.partition_functions AS pf 
    INNER JOIN sys.partition_range_values prv ON prv.function_id=pf.function_id
    WHERE (pf.name=N'PartitionFunctionName');

WHILE EXISTS (SELECT NULL FROM @Partitions)
    BEGIN

        SELECT TOP 1 @PartitionId = PartitionId FROM @Partitions;

        ALTER TABLE MS_PROD SWITCH PARTITION @PartitionId TO MS_Stage PARTITION @PartitionId;

        RAISERROR('Switched PartitionId %d to Stage',0,1,@PartitionId) WITH NOWAIT;

        DELETE @Partitions WHERE PartitionId = @PartitionId;

    END

Leave a Reply

Your email address will not be published. Required fields are marked *