O'Reilly logo

Microsoft SQL Server 2012 Bible by Aaron Nelson, Jose Chinchilla, Patrick LeBlanc, Jorge Segarra, Adam Jorgensen

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required