Microsoft SQL Server 2012 Bible

Partitioned Views

The concept of partitioned views was introduced with SQL Server 2000. You can partition a view locally, which means that all the underlying tables are stored on the same SQL Server instance. You can also base a view on tables distributed across multiple servers, which is known as a distributed partitioned view. The databases are said to be federated. You can use linked servers to union all the data through views.

With the data split into several member tables, of course, each individual table may be directly queried. A more sophisticated and flexible approach is to access the whole set of data by querying a view that unites all the member tables — this type of view is called a partitioned view.

The SQL Server query processor is designed specifically to handle such a partitioned view. If a query accesses the union of all the member tables, the query processor can retrieve data only from the required member tables.

A partitioned view not only handles selects, but also data can be inserted, updated, and deleted through the partitioned view. The query processor engages only the individual tables necessary.

Note

The individual tables underneath the partitioned view are called member tables, not to be confused with partitioned tables, a completely different technology, covered in the next major section in this chapter.

Local-Partition Views

Local-partition views access only local tables. For a local-partition view to be configured, the following elements must ...