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.


Related Articles

Implementing Table Interfaces (19 May 2008)

Implementing Table Inheritance in SQL Server (20 February 2008)

Custom Auto-Generated Sequences with SQL Server (24 April 2007)

The Case for the Surrogate Key (9 August 2002)

Using TABLE Variables (7 June 2002)

More Trees & Hierarchies in SQL (1 May 2002)

Default Constraint Names (24 January 2001)

Temporary Tables (17 January 2001)

Other Recent Forum Posts

How to output data from rows in different tables into one text file with each row having different value types (18h)

SQL query (19h)

Customers with no Orders (1d)

How to write SQL query to export custom data (1d)

Merge two fields from one table with one field from another table (2d)

Regarding SQL server agent not working (4d)

How to join 2 tables with many-to-one relationship (4d)

Table normalization, I can’t remove duplicates (5d)

- Advertisement -