Microsoft® SQL Server® 2012 Bible

1126

Part VIII: Performance Tuning and Optimization

ADD CONSTRAINT SalesOrderDetailNewPK

PRIMARY KEY NONCLUSTERED (SalesOrderDetailID, OrderDate)

GO

CREATE CLUSTERED INDEX CIX_SalesOrderDetailNew_OrderDate

ON dbo.SalesOrderDetailNew (OrderDate)

GO

The following adds the mandatory constraint:

ALTER TABLE dbo.SalesOrderDetailNew

ADD CONSTRAINT SODNewPT

CHECK (OrderDate BETWEEN '1/1/2009' AND '12/31/2009')

Now import the new data from AdventureWorks, reusing the 2008 data:

INSERT INTO dbo.SalesOrderDetailNew

(

SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty,

ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount,

LineTotal, OrderDate

)

SELECT

SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty,

ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount,

LineTotal, DATEADD(YEAR,1,ModifiedDate)

FROM Sales.SalesOrderDetail

WHERE ModifiedDate BETWEEN '1/1/2008' AND '12/31/2008'

The new table now has 45,576 rows.

Prepping the Partition Table

The original partition table, built earlier in this section, has a nonpartitioned, nonclus-

tered primary key. Because one of the rules of switching into a partitioned table is that

every index must be partitioned, the fi rst task for this example is to drop and rebuild the

SalesOrderDetail table’s primary key so it will be partitioned:

ALTER TABLE dbo.SalesOrderDetail

DROP CONSTRAINT SalesOrderDetailPK

GO

ALTER TABLE dbo.SalesOrderDetail

ADD CONSTRAINT SalesOrderDetailPK

PRIMARY KEY NONCLUSTERED (SalesOrderDetailID, OrderDate)

ON psOrderYearsFiles(OrderDate)

GO

Next, the partition table needs an empty partition:

ALTER PARTITION SCHEME psOrderYearsFiles

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

http://www.it-ebooks.info