You are a database developer on an instance of SQL Server 2008. Your company has branch offices located in different geographic regions. Each branch office maintains a separate SQL Server instance that contains a TransactionHistory table, which contains a historical record of that branch’s transactions. Each branch office’s TransactionHistory table has the same table structure, except each table contains a CHECK constraint on the BranchID column that allows branch users to only add and update records for their specific branch.
You want to allow users at the corporate office to query transaction history from all branches. Most queries will access only transactions for a single branch office, but some queries will access transaction history across multiple branches based on a date range.
You want ensure that branch office users can only modify the appropriate data, but also provide optimum performance for queries.
Which action should you take?
A. Implement a nested view that exposes the required data.
B. Create a view for each branch office that includes the WITH CHECK OPTION clause.
C. Create a distributed partitioned view using the UNION ALL operator.
D. Create a single partitioned table that includes a single CHECK constraint and transaction history from all branch offices.
Correct Answer: C
Explanation/Reference:
Partitioned views are used when you 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 you had three separate tables with an identical structure on the same server, you 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, you have multiple TransactionHistory tables residing on separate servers, each of which contains a subset of transaction history data. You 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.
You 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, you should avoid nested views when possible, and if you use nested views, you should limit the level of nesting where possible.
You should not create a view for each branch office that includes the WITH CHECK OPTION clause. If you 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.
You 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. You might choose to use a single partitioned table if all the base tables resided on the same server.
