1111
Chapter 49: Partitioning
49
The individual tables underneath the partitioned view are called member tables, not to be confused with partitioned
tables, a completely different technology, covered in the next major section in this chapter.Local-Partition Views
Local-partition views access only local tables. For a local-partition view to be confi gured,
the following elements must be in place:■ (^) The data must be segmented into multiple tables according to a single column,
known as the partition key.
■ (^) Each partition table must have a check constraint restricting the partition-key
data to a single value. SQL Server uses the check constraint to determine which
tables are required by a query.
■ The partition key must be part of the primary key.
■ (^) The partition view must include a union statement that pulls together data from all
the partition tables.
Segmenting the Data
To implement a partitioned-view design for a database and segment the data in a logical
fashion, the fi rst step is to move the data into the member tables.
Using the SalesOrderHeader and SalesOrderDetail tables from the AdventureWorks
database, the data is partitioned by Country. The data is broken down as follows:
SELECT st.CountryRegionCode Country, count(*) Count
FROM Sales.SalesOrderHeader soh
INNER JOIN Sales.SalesOrderDetail sod
ON soh.SalesOrderID = sod.SalesOrderID
INNER JOIN Sales.SalesTerritory st
ON soh.TerritoryID = st.TerritoryID
GROUP BY st.CountryRegionCode
Result:
Country Count
DE 7528
GB 10426
AU 15058
CA 19064
FR 9088
US 60153
To partition the sales data, the SalesOrderHeader and SalesOrderDetail tables split
into a table for each country. The fi rst portion of the script creates the partition tables.
c49.indd 1111c49.indd 1111 7/31/2012 10:24:22 AM7/31/2012 10:24:22 AM
http://www.it-ebooks.info