Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Apr 2, 2004

MS SQL Server Distributed Partitioned Views Part 2 - Page 2

By Don Schlichting

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM