0

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?

  • The target table should be in the same filegroup as the partition to switch. Therefore, if the source table is partitioned through different filegroups, you can't use the same target table. – irimias Nov 17 '16 at 15:57
1

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
  • What I was thinking was I could switch all partitions existing on source table to empty non-partitioned table. Further reading, it looks like since the source is partitioned, then I can only use the switch with one partition at a time. – srd211 Nov 17 '16 at 19:25
  • Correct - if you are using SWITCH, you can only reference ONE partition at a time. – Scott Hodgin Nov 17 '16 at 19:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.