Using Views with Partitioned Data

Partitioned views allow the data in a large table to be split into subtables. The data is partitioned between the subtables based on ranges of data values in one of the columns. The data ranges for each subtable are defined in a CHECK constraint specified on the partitioning column. A view is then defined that uses UNION ALL to combine selects of all the subtables into a single result set. When SELECT statements referencing the view specify a search condition on the partition column, the query optimizer uses the CHECK constraint definitions to determine which subtable contains the rows.

For example, a sales table that records sales for 1998 has been partitioned into 12 subtables, one for each month. Each subtable has a constraint defined on the OrderMonth column:

CREATE TABLE May1998Sales

    (OrderID    INT        PRIMARY KEY,

    CustomerID        INT            NOT NULL,

    OrderDate        DATETIME        NULL

        CHECK (DATEPART(yy, OrderDate) = 1998)

    OrderMonth        INT

        CHECK (OrderMonth = 5),

    DeliveryDate        DATETIME        NULL,

        CHECK (DATEPART(mm, OrderDate) = OrderMonth)

    )

The application populating May1998Sales must ensure all rows have 5 in the OrderMonth column and the order date specifies a date in May, 1998. This is enforced by the constraints defined on the table.

A view is then defined that uses UNION ALL to select the data from all 12 tables as a single result set:

CREATE VIEW Year1998Sales

AS

SELECT * FROM Jan1998Sales

UNION ALL

SELECT * FROM Feb1998Sales

UNION ALL

SELECT * FROM Mar1998Sales

UNION ALL

SELECT * FROM Apr1998Sales

UNION ALL

SELECT * FROM May1998Sales

UNION ALL

SELECT * FROM Jun1998Sales

UNION ALL

SELECT * FROM Jul1998Sales

UNION ALL

SELECT * FROM Aug1998Sales

UNION ALL

SELECT * FROM Sep1998Sales

UNION ALL

SELECT * FROM Oct1998Sales

UNION ALL

SELECT * FROM Nov1998Sales

UNION ALL

SELECT * FROM Dec1998Sales

The SQL Server query optimizer recognizes that the search condition in this SELECT statement references only rows in the May1998Sales and Jun1998Sales tables, and limits its search to those tables:

SELECT *

FROM Year1998Sales

WHERE OrderMonth IN (5,6) AND CustomerID = 64892

CHECK constraints are not strictly needed for the partitioned view to return the correct results. However, if the CHECK constraints have not been defined, the query optimizer must search all the tables instead of only those that cover the search condition on the partitioning column. Without the CHECK constraints the view operates like any other view with UNION ALL. The query optimizer cannot make any assumptions about the values stored in different tables and it cannot skip searching the tables that participate in the view definition.

Another benefit of partitioned views is that they make it easier to maintain the subtables independently. For example, at the end of a period:

• The definition of the partitioned view for current results can be changed to add the newest period and drop the oldest period.
• The definition of the partitioned view for past results can be changed to add the period just dropped from the current results view. The past results view can also be updated to remove and archive the oldest period it covers.

When you insert data into the partitioned views, the sp_executesql system stored procedure can be used to create INSERT statements with execution plans that have a high chance of being reused in systems with many concurrent users.

See Also