1112
Part VIII: Performance Tuning and Optimization
They differ from the original tables only in the primary-key defi nition, which
becomes a composite primary key consisting of the original primary key and the
CountryRegionCode. In the SalesOrderDetail table the CountryRegionCode column
is added so that it can serve as the partition key, and the SalesOrderID column foreign-
key constraint points to the partition table.The script then progresses to populating the tables from the nonpartition tables. To
select the correct SalesOrderDetail rows, the table needs to be joined with the
SalesOrderHeaderUS)) table.For brevity’s sake, only the United States (US) region is shown here. The chapter’s sample
code script includes similar code for all the other regions.IF(OBJECT_ID('dbo.SalesOrderDetailUS')) IS NOT NULL
DROP TABLE dbo.SalesOrderDetailUS
GO
IF(OBJECT_ID('dbo.SalesOrderHeaderUS')) IS NOT NULL
DROP TABLE dbo.SalesOrderHeaderUS
GO
--SalesOrderTable
CREATE TABLE dbo.SalesOrderHeaderUS
(
CountryRegionCode varchar(5) NOT NULL,
SalesOrderID int NOT NULL,
SalesOrderNumber varchar(25) NOT NULL,
CustomerID int NOT NULL,
SalesPersonID int NOT NULL,
OrderDate datetime NOT NULL
) ON [PRIMARY ]
GO
--PK
ALTER TABLE dbo.SalesOrderHeaderUS
ADD CONSTRAINT PK_SalesOrderHeaderUS PRIMARY KEY NONCLUSTERED
(CountryRegionCode, SalesOrderID)
GO
--Check Constraint
ALTER TABLE dbo.SalesOrderHeaderUS
ADD CONSTRAINT SalesOrderHeaderUS_PartitionCheck CHECK
(CountryRegionCode = 'US')
GO--SalesOrderDetail Table
CREATE TABLE dbo.SalesOrderDetailUS
(
CountryRegionCode varchar(5) NOT NULL,
SalesOrderDetailID int NOT NULL,
SalesOrderID int NOT NULL,
ProductID int NOT NULL,c49.indd 1112c49.indd 1112 7/31/2012 10:24:22 AM7/31/2012 10:24:22 AM
http://www.it-ebooks.info