Partitioned views

Partitioned views allow the data in a large table to be split into smaller member tables. The data is partitioned between the member tables based on ranges of data values in one of the columns. The data ranges for each member table are defined in a CHECK constraint specified on the partitioning column. A view that uses UNION ALL to combine selects of all the member tables into a single result set is then defined. 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 member table contains the rows.

To perform updates on a partitioned view, the partitioning column must be a part of the primary key of the base table. If a view is not updatable, you can create an INSTEAD OF trigger on the view that allows updates. You should design error handling into the trigger to make sure that no duplicate rows are inserted.

CHECK constraints are not 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.

If all the member tables referenced by a partitioned view are on the same server, the view is a local partitioned view. If the member tables are on multiple servers, the view is a distributed partitioned view. Distributed partitioned views can be used to spread the database processing load of a system across a group of servers. 

Partitioned views make it easier to maintain the member tables independently. For example, you can do the following 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 significant chance of being reused in systems with many concurrent users.

Limitations

Tables referenced in updateable partitioned views cannot have identity column.

Tables cannot have computed columns if you want to have updateable partitioned view.

The partitioning column has to be defined using equality or inequality operators.