Microsoft® SQL Server® 2012 Bible

1114

Part VIII: Performance Tuning and Optimization

sod.CarrierTrackingNumber

FROM Sales.SalesOrderDetail sod

INNER JOIN dbo.SalesOrderHeaderUS soh

ON sod.SalesOrderID = soh.SalesOrderID

Creating the Partition View

With the data split into valid partition tables that include the correct primary keys and

constraints, SQL Server can access the correct partition table through a partition view. The

SalesOrderAll view uses a UNION ALL to vertically merge data from all three partition

tables:

CREATE VIEW SalesOrderAll

AS

SELECT CountryRegionCode, SalesOrderID, SalesOrderNumber, CustomerID,

SalesPersonID, OrderDate

FROM SalesOrderHeaderUS

UNION ALL

SELECT CountryRegionCode, SalesOrderID, SalesOrderNumber, CustomerID,

SalesPersonID, OrderDate

FROM SalesOrderHeaderCA

UNION ALL

SELECT CountryRegionCode, SalesOrderID, SalesOrderNumber, CustomerID,

SalesPersonID, OrderDate

FROM SalesOrderHeaderAU

Selecting Through the Partition View

When all the data is selected from the OrderAll partition view, the query plan, as shown

in Figure 49-1, includes all three partition tables as expected:

SELECT CountryRegionCode, SalesOrderNumber

FROM SalesOrderAll

Result (abridged):

CountryRegionCode SalesOrderNumber

----------------- -------------------------

US SO43659

...
CA SO71916
CA SO71921
FR SO46623
FR SO46626

What makes partition views useful for advanced scalability is that the SQL Server query

processor can use the partition tables’ check constraints to access only the required tables

if the partition key is included in the WHERE clause of the query calling the partition view.

The following query selects on the United States sales orders from the partition view.

The CountryRegionCode column is the partition key, so this query optimizes for

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

http://www.it-ebooks.info