Microsoft® SQL Server® 2012 Bible

1123

Chapter 49: Partitioning

49

LineTotal, ModifiedDate

FROM Sales.SalesOrderDetail

Multiple partition schemes can share a single partition function. Architecturally, this might

make sense if several tables should be partitioned using the same boundaries because this

improves the consistency of the partitions. To verify which tables use which partition

schemes, based on which partition functions, use the Object Dependencies dialog for the parti-

tion function or partition scheme. You can fi nd it using the partition function’s context menu.

For information about how the partitions are used, look at sys.partitions and sys.

dm_db_partition_stats.

Querying Partition Tables

The nice thing about partition tables is that no special code is required to query either

across multiple underlying partition tables or from only one partition table. The Query

Optimizer automatically uses the right tables to retrieve the data.

The $partition operator can return the partition table’s integer identifi er when used

with the partition function. The next code snippet counts the number of rows in each

partition:

SELECT $PARTITION.pfyearsfnOrderYears(OrderDate) AS Partition,

COUNT(*) AS Count

FROM SalesOrderDetail

GROUP BY $PARTITION.pfyearsfnOrderYears(OrderDate)

ORDER BY Partition;

Result:

Partition Count

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

1 5151

2 19353

3 51237

4 45576

The next query selects data for one year, so the data should be located in only one

partition. Examining the query execution plan (not shown here) reveals that the Query

Optimizer used a high-speed clustered index scan on partition ID PtnIds1005:

SELECT *

FROM dbo.SalesOrderDetail

WHERE OrderDate BETWEEN '1/1/2005' AND '12/31/2005'

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

http://www.it-ebooks.info