1125
Chapter 49: Partitioning
49
Switching Tables
Switching tables is the cool capability to move an entire table into a partition within a par-
titioned table or to remove a single partition so that it becomes a standalone table. This is
useful when importing new data, but following are a few restrictions:■ Every index for the partition table must be a partitioned index.■ (^) The new table must have the same columns (excluding identity columns), indexes,
and constraints (including foreign keys) as the partition table, except that the new
table cannot be partitioned.
■ The source partition table cannot be the target of a foreign key.
■ (^) Neither table can be published using replication or have schema-bound views.
■ The new table must have check constraint restricting the data range to the new
partition, so SQL Server doesn’t need to reverify the data range. (And it needs to be
validated; no point loading and then creating the constraint with nocheck.)
■ (^) Both the standalone table and the partition that receives the standalone table
must be on the same fi legroup.
■ (^) The receiving partition or table must be empty.
In essence, switching a partition is rearranging the database metadata to reassign the
existing table as a partition. No data is actually moved, which makes table switching nearly
instantaneous regardless of the table’s size.
Prepping the New Table
You can create the SalesOrderDetailNew table to demonstrate switching. It holds all the
2009 data from the AdventureWorks database:
CREATE TABLE [dbo].[SalesOrderDetailNew]
(
[SalesOrderID] [int] NOT NULL,
[SalesOrderDetailID] [int] NOT NULL,
[CarrierTrackingNumber] nvarchar NULL,
[OrderQty] [smallint] NOT NULL,
[ProductID] [int] NOT NULL,
[SpecialOfferID] [int] NOT NULL,
[UnitPrice] [money] NOT NULL,
[UnitPriceDiscount] [money] NOT NULL,
[LineTotal] money,
[OrderDate] [datetime] NOT NULL
)
ON [AdventureWorks_SalesOrderDetail2009Partition];
You can create indexes identical to those on the preceding table on the partitioned table:
ALTER TABLE dbo.SalesOrderDetailNew
c49.indd 1125c49.indd 1125 7/31/2012 10:24:24 AM7/31/2012 10:24:24 AM
http://www.it-ebooks.info