Horizontal and Vertical Partitioning

SQL Server's replication feature lets you reproduce data from one database to another. A major component of SQL Server's replication technology involves horizontal (row) and vertical (column) partitioning, which lets you control what data you replicate. Once you understand horizontal and vertical partitioning, you can streamline how you store and distribute data from your SQL Server databases.

Setting Up Replication

Replication is built into SQL Server, version 6.0 and newer. It lets you take information from one source and copy it to other destinations. This powerful feature is simple to enable and maintain.(See also, "Microsoft SQL Server 7.0 to replicate data over the Net").

To send data from your system to other systems, you publish the data on the source machine. For example, to distribute data from server VSI10 to other machines, you begin by installing Publishing on VSI10, as you see in Screen 1 (page 124).

SQL Server uses a dedicated database, the distribution database, as a repository of replication information. The distribution database holds replicated information until the system passes it to the appropriate subscribers. Note that users can't access this database; it is for system use only.

After you install the replication publishing, you determine the publishing options on the VSI10 system, as you see in Screen 2 (page 124). In this dialog box, I've set SQL Server to publish information from the tracking database and to let the VSI97 machine subscribe to this data. The next step is to create a publication. Publications consist of articles, which are the data to replicate.

For example, suppose you want to maintain an application that tracks sales leads by location, as you see in Table 1 (page 124). You create a new publication to replicate the data in this table. Screen 3 (page 124) shows the dialog box for editing the new publication. After you create the publication, you can use horizontal, vertical, and combined partitioning to selectively distribute articles for this publication.

Horizontal Partitioning

When you use a horizontal rule to partition a table, SQL Server determines what articles to include in the publication according to certain values in each row. In the sales leads example, the system's environment consists of a central location (server VSI10), where you enter inbound sales calls into a database. You then have several secondary, read-only systems where your sales representatives run reports to locate new leads. The application uses the territory_code field to determine what information to present to the users. With this functionality, you can selectively replicate only those rows you need and limit each site to receive only the sales leads for that region. This feature lets you eliminate sending extra data from other regions and avoid slowing down network traffic and taking up unnecessary space on each system. You configure this option when you set up replication by filling in the Restriction Clause dialog box, as you see in Screen 4.

The Restriction Clause portion of the dialog box is where you can tell SQL Server what rules to apply when determining how to replicate information. In this case, you create an article holding rows with a territory_code between 0 and 1000. You can then replicate this article to appropriate machines. You can create as many articles as necessary and replicate them to the right locations.

Vertical Partitioning

In contrast to horizontal partitioning, vertical partitioning lets you restrict which columns you send to other destinations, so you can replicate a limited subset of a table's columns to other machines. Imagine that the sales leads table has an extra column, revenue_
potential, as you see in Table 2.

The revenue_potential column contains sensitive information that you don't want your users to see. You can create a view that doesn't show the column, you can restrict permissions, or you can block user access to this column by not replicating it. To keep from replicating this column, you clear the Replicate check box for the revenue_potential column, as you see in Screen 5, when you configure the replication. Keep in mind that you must always replicate the primary key columns.

Combined Horizontal and Vertical Partitioning

SQL Server lets you combine horizontal and vertical partitioning when replicating. So for the sales leads example, you can easily use the territory_code value to restrict replication of the lead_location_
Table and instruct the engine not to replicate the revenue_potential column, regardless of the territory_code value. Screen 6 shows both changes in effect. For more information about partitioning and horizontal and vertical replication, see my book, Microsoft SQL Server: Designing and Building a High Performance Database, Prentice Hall, ISBN 0-132-66222-1.

Learn more by visiting SQL Server Pro online.

Using a SQL Server feature to control database distribution

TABLE 1: Tracking Sales Leads

lead_idlast_name...territory_code 556709Robinson...1002 556710Schmieding...7595 ...... 602551Palomino...4465

TABLE 2: Sales Leads with Revenue Potential

lead_idlast_name...territory_coderevenue_potential 556709Robinson...1002 35000 556710Schmieding...7595500000 ..... 602551Palomino...4465250000