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.


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

T-sql - we created Message from app1 and trying to disable from app2 (6h)

SQL select Top 10 records for unique combination of two columns (20h)

SSRS Report Sorting with Grouping Issue (2d)

ORA-01476: divisor is equal to zero (2d)

Create new columns based on min and max values of a record with multiple rows (2d)

Memory Required for Reporting Services 2022 (2d)

Backup sql server large db on cloud (2d)

Selecting x columns but only displaying y columns (2d)

- Advertisement -