Microsoft® SQL Server® 2012 Bible

1127

Chapter 49: Partitioning

49

NEXT USED [AdventureWorks_SalesOrderDetail2009Partition]

GO

ALTER PARTITION FUNCTION pfyearsfnOrderYears()

SPLIT RANGE ('12/31/2009')

GO

Performing the Switch

The ALTER TABLE.. .SWITCH TO command moves the new table into a specifi c partition.

ALTER TABLE SalesOrderDetailNew

SWITCH TO SalesOrderDetail PARTITION 5

Switching Out

You can use the same technology to switch a partition out of the partition table so that

it becomes a standalone table. Because no merge takes place, this is much easier than

switching in. The following code takes the fi rst partition out of the SalesOrderDetail

partitioned table and reconfi gures the database metadata so that it becomes its own

table:

CREATE TABLE [dbo].[SalesOrderDetailArchive]

(

[SalesOrderID] [int] NOT NULL,

[SalesOrderDetailID] [int] NOT NULL,

[CarrierTrackingNumber] [nvarchar](25) 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];

CREATE CLUSTERED INDEX CIX_SalesOrderDetailArchive_OrderDate

ON dbo.SalesOrderDetailArchive(OrderDate)

ON [AdventureWorks_SalesOrderDetail2009Partition];

ALTER TABLE SalesOrderDetail

SWITCH PARTITION 5 to SalesOrderDetailArchive

Rolling Partitions

With a little imagination, you can use the technology to create and merge existing parti-

tions to create rolling partition designs.

c49.indd 1127c49.indd 1127 7/31/2012 10:24:25 AM7/31/2012 10:24:25 AM

http://www.it-ebooks.info