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.

USE master
GO
EXEC sp_addlinkedserver
	@server = ‘server2’,
	@srvproduct = ‘SQLServer OLEDB Provider’,
	@provider = ‘SQLOLEDB’,
	@datasrc = ‘InfoNet’
GO
EXEC sp_addlinkedsrvlogin ‘server2’, ‘false’, ‘sa’, ‘sa’, ‘secret’
GO
SELECT *
FROM server2.pubs.dbo.authors

All the rows from the authors
table should have been returned. Log in to Server2 as sa and execute the
following code:

USE master
GO
EXEC sp_addlinkedserver
	@server = ‘server1’,
	@srvproduct = ‘SQLServer OLEDB Provider’,
	@provider = ‘SQLOLEDB’,
	@datasrc = ‘dons13’
GO
EXEC sp_addlinkedsrvlogin ‘server1’, ‘false’, ‘sa’, ‘sa’, ‘secret’
GO
SELECT *
FROM server1.pubs.dbo.authors

For an explanation of the above
code, see Part1.

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:

CREATE DATABASE test
GO
USE test
GO
CREATE TABLE [dbo].[OrdersUnder] (
	[ord_nbr] [int] NOT NULL ,
	[ord_date] [datetime] NOT NULL ,
	[cust_id] [int] NOT NULL,
	[amount] [money] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[OrdersUnder] WITH CHECK ADD
	CONSTRAINT [PK_ord_nbr] PRIMARY KEY  CLUSTERED
	(
		[ord_nbr]
	)  ON [PRIMARY]
GO
ALTER TABLE [dbo].[OrdersUnder] WITH CHECK ADD
	CONSTRAINT [CHK_Under] CHECK ([ord_nbr] <= 1000)
GO

From Server2, the code is almost
identical. Only the name and constraint changes:

CREATE DATABASE test
GO
USE test
GO
CREATE TABLE [dbo].[OrdersOver] (
	[ord_nbr] [int] NOT NULL ,
	[ord_date] [datetime] NOT NULL ,
	[cust_id] [int] NOT NULL,
	[amount] [money] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[OrdersOver] WITH CHECK ADD
	CONSTRAINT [PK_ord_nbr] PRIMARY KEY  CLUSTERED
	(
		[ord_nbr]
	)  ON [PRIMARY]
GO
ALTER TABLE [dbo].[OrdersOver] WITH CHECK ADD
	CONSTRAINT [CHK_Over] CHECK ([ord_nbr] > 1000)
GO

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:

Server: Msg 4433, Level 16, State 4, Line 1
Cannot INSERT into partitioned view ‘ViewName’
  because table ‘[TableName]’ has an IDENTITY constraint.