dcsimg

MS SQL Server Distributed Partitioned Views Part 2

April 2, 2004



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.







The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers