Using Partitioned Views

The partitioned view allows you to split the data in a large table into smaller member tables. According to a range of data values, the data to be partitioned between the member tables. The range of data for each member of the table are defined in a CHECK constraint specified for partitioning column. Then define a view that uses UNION ALL the selected member tables into a single result set. The SELECT statement referencing the view for the partitioning column specified search criteria, the query optimizer will use the CHECK constraint definitions to determine which member table contains the corresponding row.

The preferred method of the data on the server partition is the partition table. For more information, please refer to .

For example, the record1998sales sales table is partitioned into 12 members of the table, a month table. Each member table has constraint defined inOrderMonth:

  CREATE TABLE May1998Sales to 
    (OrderID INT, 
    CustomerID INT NOT NULL, 
    OrderDate DATETIME NULL 
       CHECK (DATEPART (yy, OrderDate) = 1998) 
    OrderMonth INT 
       CHECK (OrderMonth, = 5), 
    DeliveryDate DATETIME NULL 
       CHECK (DATEPART (mm DeliveryDate) in = 5) 
    CONSTRAINT OrderIDMonth PRIMARY KEY (OrderID, OrderMonth) 

FillMay1998salesapplication must ensure that the value of all the rows in the theOrderMonthcolumn , specify a date in mid-May 1998 and the order date.5This enforce constraints defined by the table.

Then, define a view, the view using theUNION ALLselect data from all 12 member tables as a single result set:

  CREATE VIEW Year1998Sales to 
 SELECT * FROM Jan1998Sales 
 SELECT * FROM Feb1998Sales 
 SELECT * FROM Mar1998Sales 
 SELECT * FROM Apr1998Sales 
 SELECT * FROM May1998Sales 
 SELECT * FROM Jun1998Sales 
 SELECT * FROM Jul1998Sales 
 SELECT * FROM Aug1998Sales 
 SELECT * FROM Sep1998Sales 
 SELECT * FROM Oct1998Sales 
 SELECT * FROM Nov1998Sales 
 SELECT * FROM Dec1998Sales 

For example, the followingSELECTstatement to query information about a specific month.

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

The SQL Server query optimizer can be identified theSELECTstatement search criteria the only referencesMay1998Salestable andJun1998Salestable in the line. Therefore, it will limit the search scope in these tables.

Partitioned views updated partitioning column must be part of the primary key of the base table. If the view is not updatable view that allows updates to create an INSTEAD OF trigger. Should trigger design error handling to ensure that no duplicate rows are inserted. For a design example of an INSTEAD OF trigger on a view, see .

The partitioned view returns the correct result does not necessarily require a CHECK constraint. However, If you do not define a CHECK constraint, the query optimizer must search all the tables, rather than search only meet the partition table based on the column on the search criteria. If you do not use the CHECK constraint, the view operates in the same way as any other use UNION ALL view. The query optimizer can not make any assumption as to the value stored in different tables can not skip the table involved in the view definition search.

If the partition all the member tables referenced by the view on the same server, the view is a local partitioned view. If the member tables on multiple servers, the view is distributed partitioned view. Distributed partitioned view can be used for distributed database processing load among a group of servers. For more information, please refer to the .

Partitioned view can be more easily maintained independently of the members of the table. For example, at the end of a period can do the following:

  • Can change the results of the partitioned view definition to add the latest stage and delete the earliest possible stage.
  • Partitioned view definitions can change the previous results to add just the current results view deleted stage. You can also update the previous results view to delete or archive the views contained in the earliest stages.

Insert the data into a partitioned view, you can use the sp_executesql system stored procedure to create the INSERT statement, the statement contains a higher risk of execution plan re-use in systems with many concurrent users.