MS SQL Server Distributed Partitioned Views Part 2 - Page 2
April 2, 2004
This view is a simple select pulling data from both servers into one result set. From Server1, create a view with the following statement:
CREATE VIEW AllOrders AS SELECT * FROM OrdersUnder UNION ALL SELECT * FROM server2.test.dbo.OrdersOver
For Server2, again the code is almost identical:
CREATE VIEW AllOrders AS SELECT * FROM OrdersOver UNION ALL SELECT * FROM server1.test.dbo.OrdersUnder
There are a couple of rules for DML Views. For a view to be updatable, all the columns included in the primary key must be returned. Any columns not included in the view, must allow Nulls.
Test the view by running a simple select:
SELECT * FROM AllOrders
An empty record set should be returned. Before starting any DML statements, there are a couple more items to include.
Distributed Transaction Coordinator
Before beginning the examples, we need to start the Distributed Transaction Coordinator. The DTC manages the committing of transactions when there are several different data sources involved. For Windows 2000, service pack 1 is required.
Open the services MMC, locate and start the Distributed Transaction Coordinator using the default settings.
Lazy Schema Validation
Although not a requirement, setting lazy schema validation will increase query performance. Schema validation is the act of checking the remote schema to validate its metadata. By setting the validation to lazy, SQL will not validate the remote metadata against our query until execution. If there has been a schema change on the remote, our query will error out. In our case, we know the remote table is valid, with the same structure as our local table. We will pickup a gain by not checking remote schema.
use master GO sp_serveroption 'LocalServerName', 'lazy schema validation', true GO sp_serveroption 'server2', 'lazy schema validation', true GO
If we assure SQL that the sort and character set are the same between the local and remote servers, then the remote can participate in comparisons. Otherwise, all the data will come across the wire, and be returned to the local server. All comparisons will then be done locally, degrading performance. This can be avoided by setting the collation compatible option to true. Again, this is not a requirement for distributed queries, but an optimization option.
use master GO sp_serveroption 'LocalServerName', 'collation compatible', true GO sp_serveroption 'server2', 'collation compatible', true GO
Let's begin by inserting data into the new empty table. The following statement will insert one record:
use test GO SET XACT_ABORT ON GO INSERT INTO AllOrders (ord_nbr, ord_date, cust_id, amount) VALUES (1001, '01/01/1993', 5, 50.25)
The insert itself is the same transact SQL used regularly. XACT_ABORT is required for data modification statements. When set ON, any run time error will cause the entire transaction to roll back.
Selecting from our view will verify the record has been written successfully:
Deleting records involves the same steps:
SET XACT_ABORT ON GO DELETE FROM AllOrders WHERE cust_id = 5
The newly inserted record is now deleted. Notice the WHERE clause does not need to reference our primary key or check constraint column.
Odds and Ends
The view can reference the linked table by four part naming (as our example has done), by using the OPEN ROWSET function, or the OPENDATASOURCE function.
Insert and update are not allowed on tables containing a timestamp column.
For a complete list of view rules, see BOL "partitioned views."
Although we now have two tables, on two different servers, acting as one, there is no automatic way to backup, or restore them, as one unit.
Federated Databases along with Distributed Partitioned Views can increase performance on very large tables. Care and planning will be required, especially for DML operations. Nevertheless, the performance gains can be worth the extra administration.