Featured Database Articles

MS SQL Server Distributed Partitioned Views Part 2

In this second article, the use of Distributed Partitioned
Views for Insert, Update, and Delete statements will be explored.

Introduction

In Part1,
the basics of Distributed Partitioned Views,
Federated Databases, and Horizontal Partitioning
were reviewed. A sample Linked Server, a partitioned table and a view using
the UNION operator were created. In this second part, a new sample demonstrating
DML statements (INSERT, UPDATE, and DELETE), will
be developed.

Links

We will start by creating a test
environment. The examples will be created using two machines, each running SQL
2000 on Windows 2000. Although there will only be two machines in our
examples, the same rules would apply for three or more.

Begin by creating reciprocal
Linked Servers, from Server1 to Server2, and from Server2 to Server1. Log in
to Server1 as sa and execute the following code.

Tables

With the Linked Servers created, the
next step is to create sample tables. Imagine an orders table containing
gigabits of past sales history, and reporting performance is getting sluggish.
We are going to split this large table in half. Server1 will house sales with
order numbers less than 1,000. Orders greater than 1,000 will be stored on
Server2.

From Server1 execute these
statements from master to create the test table:

There are several items to note.
First, a Check Constraint is required. The Query Optimizer uses the Check to
determine on which server the data should be located. The check must allow data
to be directed to one, and only server.

Second, a Primary Key is required
for DML. Selects do not require a key. However, for Inserts, Updates and
Deletes, the key must be present. In addition, the Key cannot be created as an
Auto Identity field. Using one will produce the error: