1122
Part VIII: Performance Tuning and Optimization
For information about partition schemes programmatically, query sys.partition_schemes.Creating the Partitioned Table
Now that all the required elements are in place, you can create the table and indexes
that use them. First, create your table with a nonclustered primary key. Then add a
clustered index, which partitions the table based on the partition scheme. Because
partition functions and schemes don’t have owners, you do not need to refer to the
owners in the name.The following table is similar to the AdventureWorks2012 SalesOrderDetail table in
the sales scheme:CREATE TABLE [dbo].[SalesOrderDetail]
(
[SalesOrderID] [int] NOT NULL,
[SalesOrderDetailID] [int]NOT NULL,
CONSTRAINT SalesOrderDetailPK PRIMARY KEY
NONCLUSTERED(SalesOrderDetailID),
[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
);
CREATE CLUSTERED INDEX CIX_SalesOrderDetail_OrderDate
ON dbo.SalesOrderDetail(OrderDate)
ON psOrderYearsFiles(OrderDate)The next script inserts data from the Sales.SalesOrderDetail table into the newly cre-
ated partitioned dbo.SalesOrderDetail table.INSERT INTO dbo.SalesOrderDetail
(
SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber,
OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount,
LineTotal, OrderDate
)
SELECT
SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber,
OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount,c49.indd 1122c49.indd 1122 7/31/2012 10:24:24 AM7/31/2012 10:24:24 AM
http://www.it-ebooks.info