MS SQL Server Distributed Partitioned Views Part 2 - Page 2

Views

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

Collation Compatible

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

DML

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.

Conclusion

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.

» See All Articles by Columnist Don Schlichting