Switching Partitions - Example

From SQLServerPedia

Jump to: navigation, search

See Also: Main_Page - Database Administration - Database Design - Partitioning

To demonstrate how partition switching works, let's continue with the example presented Creating Partitioned Tables by using the same partition function FullOrderDateRangePScheme for a secondary table: 

CREATE TABLE [dbo].[FactInternetSales_Partitioned2](
 [ProductKey] [int] NOT NULL,
 [OrderDateKey] [int] NOT NULL,
 [DueDateKey] [int] NOT NULL,
 [ShipDateKey] [int] NOT NULL,
 [CustomerKey] [int] NOT NULL,
 [PromotionKey] [int] NOT NULL,
 [CurrencyKey] [int] NOT NULL,
 [SalesTerritoryKey] [int] NOT NULL,
 [SalesOrderNumber] [nvarchar](20) NOT NULL,
 [SalesOrderLineNumber] [tinyint] NOT NULL,
 [RevisionNumber] [tinyint] NULL,
 [OrderQuantity] [smallint] NULL,
 [UnitPrice] [money] NULL,
 [ExtendedAmount] [money] NULL,
 [UnitPriceDiscountPct] [float] NULL,
 [DiscountAmount] [float] NULL,
 [ProductStandardCost] [money] NULL,
 [TotalProductCost] [money] NULL,
 [SalesAmount] [money] NULL,
 [TaxAmt] [money] NULL,
 [Freight] [money] NULL,
 [CarrierTrackingNumber] [nvarchar](25) NULL,
 [CustomerPONumber] [nvarchar](25) NULL,
 [FullDate] [datetime] NULL
) ON FullOrderDateRangePScheme  (FullDate)

Now suppose that FactInternetSales_Partitioned2 has data for 2004, whereas FactInternetSales_Partitioned does not. You need to swap the partitions between two tables so that the main fact table has 2004 data. As a best practice, each table should have a clustered index. Although clustered indexes aren't required for partition switching, if the source table has the clustered index then the destination table must also have an identical clustered index. Execute the following statements to add a clustered index to each table: 

CREATE CLUSTERED INDEX ix_FactInternetSales_Partitioned_clustered ON FactInternetSales_Partitioned (
    OrderDateKey,
    ProductKey,
    CustomerKey)
GO
CREATE CLUSTERED INDEX ix_FactInternetSales_Partitioned_clustered ON FactInternetSales_Partitioned2 (
    OrderDateKey,
    ProductKey,
    CustomerKey)
You could use $PARTITION function to verify that 2004 values are stored on partition id 4. Next, execute ALTER TABLE statement to migrate partition 4 from the secondary table to FactInternetSales_Partitioned:

ALTER TABLE FactInternetSales_Partitioned2
SWITCH PARTITION 4 TO FactInternetSales_Partitioned PARTITION 4


In a matter of a couple of seconds partitions are switched. Note that in order to switch partitions, the partition in the destination partition must be empty.



Now you can verify the number of records in partition number 4 in FactInternetSales_Partitioned, as follows:

SELECT COUNT(*) FROM dbo.FactInternetSales_Partitioned

WHERE $PARTITION.FullOrderDateRangePFN (FullDate) = 4 ;
Result:
-----------


32265000


During partition switching, data isn't copied or duplicated. Only metadata is modified. Therefore, now that partitions have been switched the secondary table will no longer have any records for 2004. All indexes associated with a partition are also switched along with the table partition.



Similarly if you wanted to phase out obsolete records for 2001 into the secondary table we could use the following statement:

ALTER TABLE FactInternetSales_Partitioned
SWITCH PARTITION 1 TO FactInternetSales_Partitioned2 PARTITION 1


The full syntax for switching partitions is as follows: 

ALTER TABLE source_table

SWITCH [PARTITION source_partition_number]



       TO destination_table


        [PARTITION target_partition_number]


Partition schemes used by source and destination table do not have to be the same. In fact, you could switch a partition into a non-partitioned table. For example, create a non-partitioned table with a schema identical to the sample partitioned tables and execute the following statements: 
CREATE TABLE [dbo].[FactInternetSales_NonPartitioned](
    [ProductKey] [int] NOT NULL,
    [OrderDateKey] [int] NOT NULL,
    [DueDateKey] [int] NOT NULL,
    [ShipDateKey] [int] NOT NULL,
    [CustomerKey] [int] NOT NULL,
    [PromotionKey] [int] NOT NULL,
    [CurrencyKey] [int] NOT NULL,
    [SalesTerritoryKey] [int] NOT NULL,
    [SalesOrderNumber] [nvarchar](20) NOT NULL,
    [SalesOrderLineNumber] [tinyint] NOT NULL,
    [RevisionNumber] [tinyint] NULL,
    [OrderQuantity] [smallint] NULL,
    [UnitPrice] [money] NULL,
    [ExtendedAmount] [money] NULL,
    [UnitPriceDiscountPct] [float] NULL,
    [DiscountAmount] [float] NULL,
    [ProductStandardCost] [money] NULL,
    [TotalProductCost] [money] NULL,
    [SalesAmount] [money] NULL,
    [TaxAmt] [money] NULL,
    [Freight] [money] NULL,
    [CarrierTrackingNumber] [nvarchar](25) NULL,
    [CustomerPONumber] [nvarchar](25) NULL,
    [FullDate] [datetime] NULL
)
ON [Filegroup_2003]    /* note that non-partitioned table was created on filegroup_2003.  now we can switch 2003 data into the non-partitioned table:  */    ALTER TABLE FactInternetSales_Partitioned
SWITCH PARTITION 3 TO FactInternetSales_NonPartitioned


Since the destination table is not partitioned, you don't have to specify a partition number for it. If you do specify partition number for a non-partitioned table, SQL Server completes the partition switch successfully, but returns a warning similar to this:

Warning: The specified partition 3 for the table 'AdventureWorksDW.dbo.FactInternetSales_NonPartitioned' was ignored in ALTER TABLE SWITCH statement because the table is not partitioned.


The destination table remains un-partitioned. It does not inherit partition scheme from the source table. However, every table has at least one partition. So technically you could use partition switching to move data between two tables that haven't been explicitly partitioned. For example, the following statement moves data between two tables for which partitions haven't been explicitly defined; therefore partition numbers are not included: 

ALTER TABLE FactInternetSales_NonPartitioned
SWITCH TO  FactInternetSales_NonPartitioned1


You can effectively combine merge and split operations with partition switching. As an example, if you need to add the most recent data to a large fact table you could:

  1. Create a staging table on the same filegroup as the target partition, perhaps called Filegroup_2007.
     
  2. Split the most recent partition, adding a boundary value for 2nd half of 2007.
     
  3. Load data into the staging table using BULK INSERT statement or an Integration Services package. Index the staging table and ensure it contains correct data.
     
  4. Switch data from staging table into the fact table into the next to last partition.


Similarly if you were planning on removing data for 1989, which happens to be the first year for which you have data in a fact table, you could:

  1. Create an empty historical table on the same filegroup where the obsolete data resides, perhaps called Filegroup_1989.
     
  2. Switch data from the fact table to the historical table.
     
  3. Merge the first partition thereby removing the boundary point for 1989.
     
  4. Move data from the historical table to a different database. Then drop the historical table.

More SQLServerPedia Wiki Articles on SQL Server Partitioning

When To Use SQL Server Partitioning

How to Implement Partitioned Tables

Retrieved from "http://sqlserverpedia.com/wiki/Switching_Partitions_-_Example"