Horizontal partitioning in SQL7

By Chris Miller on 19 December 2000 | Tags: Table Design

Jimmy writes "How do I create a horizontal partitioning with SQL7? Horizontal partitioning is a way to break up one table into multiple partitions to increase speed and performance. I have looked at several SQL books and they said that it can be done but they do not show any coding? Do you have any SQL coding for it or know of any book that has it? Thanks."

Horizontal partitioning is a way of splitting a table (or a set of tables) into smaller pieces. This is usually accomplished by splitting the table by key values, and it is usually done to make merge replication feasible.

For example, let's take a typical table called "orders", defined like this:


create table Orders (
	OrderID  int IDENTITY(1,1),
	OfficeID int not null,
	ItemID   int not null,
	Quantity int not null,
	SalesID  int not null
)

Put a Primary Key constraint on there that includes both the OrderID and Officeid. Now, if you want to do merge replication, one way to do it is to have each office replicate in a horizontal partition of the data, defined like this:

select * from Orders where OfficeID = 42

Obviously each office would have a different office id, so one way to encapsulate this would be to create a view for each office and replicate that particular view. It's fairly simple then to set up each office as a publisher, set up an article for the Orders table, and go from there.

Now, that explains horizontal partitioning in the context of replication. How about in the context of performance for "normal" online applications?

One way to use horizontal partitioning is to establish a data warehouse, and then archive all of the records that are older than a certain date to a data warehouse server. This would leave records online that are needed for day-to-day operations, but would remove inactive records from the database, freeing the online server up from having to maintain and back up those records.

Another way to use horizontal partitioning is to break a table apart in an arbitrary fashion using one of the key values and use filegroups (or even cross-database joins) to access all of the data. This is very similar to the Orders table above, but without the replication. For example:


create table Orders42 (
	OrderID  int IDENTITY(1,1),
	OfficeID int not null,
	ItemID   int not null,
	Quantity int not null,
	SalesID  int not null
) ON Orders_Filegroup3

Then you could either create an overriding view that uses union queries to stitch everything back together, or just alter your stored procedures (your applications are just using stored procedure calls, right?) to figure out which table to insert into.

I hope this answers your question, horizontal partitioning is a fairly simple idea that has a bunch of different uses, and different methods of implementation depending on what outcome you are looking for.

rocketscientist.