SQL Server: Partitioned View

Partitioned views are used when DBA/Developer have similar data stored in multiple tables and want to create a view to allow access to all of the data as if it were stored in a single table. The tables referenced in the view can reside on the same server or on different servers. Partitioned views are implemented using the UNION ALL operator. For example, if DBA/Developer had three separate tables with an identical structure on the same server, DBA/Developer might use the following statement to create a partitioned view that allows users to query data from all three tables:

CREATE VIEW PartView

AS SELECT * FROM Table1
UNION ALL
SELECT * FROM Table2
UNION ALL
SELECT * FROM Table3;

In this scenario, DBA/Developer have multiple TransactionHistory tables residing on separate servers, each of which contains a subset of transaction history data. DBA/Developer can create a view across servers, known as a distributed partitioned view, by first creating a linked server definition for each branch and then creating the partitioned view. The partitioned view is created using a fully-qualified name in each SELECT statement. Using a distributed partitioned view would allow branch offices to access their transactions and enforce the CHECK constraint defined for each  branch's TransactionHistory table, but would also allow users at the corporate office to query all data as if it resided in a single table. In addition, if a corporate office user issued a query against one branch's transaction history, the query optimizer would use the individual CHECK constraints defined on the tables to optimize performance of the query and search only the required tables. Using partitioned views would also allow the base tables to be managed separately. This can improve availability and decentralize administration effort because each base table can be backed up, restored, reorganized, or managed individually as needed. DBA/Developer should not implement a nested view that exposes the required data. A nested view is a view that references another view in its definition. A nested view would not be appropriate in this scenario. For optimum performance, DBA/Developer should avoid nested views when possible, and if DBA/Developer use nested views, DBA/Developer should limit the level of nesting where possible. DBA/Developer should not create a view for each branch office that includes the WITH CHECK OPTION clause. If DBA/Developer created a view for each branch office, it would not maximize performance of corporate queries, and it would increase the complexity to create queries across all branches. A query that accessed transaction history across multiple branches would have to reference each branch's view. When creating a view, the WITH CHECK OPTION clause is used to ensure that no data modifications can be made through a view that would cause the underlying data to violate the view's definition. DBA/Developer should not create a single partitioned table that includes a single CHECK constraint and transaction history from all branch offices. In this scenario, leaving the transaction history data in separate tables across multiple servers will allow transaction history at each branch to be managed independently. This will provide better data availability, while still providing optimum performance of corporate queries. DBA/Developer might choose to use a single partitioned table if all the base tables resided on the same server.