Manual Vertical Partitioning

Vertical partitioning¾the replication of selected columns from the base table¾can easily be accomplished using SQL Enterprise Manager. However, as an option, you can create your own stored procedure to implement vertical partitioning.

For example, you could add a vertically partitioned article to the publication authors_publication described earlier in Setting Up Replication Manually. The article could create a phone list from data residing within the authors table in the pubs database.

To create this vertically partitioned article, add the following steps to the replication setup script created in Setting Up Replication Manually:

Create a view that will be used to bulk copy out data during synchronization. For example:
```
use pubs
go
create view phonelist_view as select au_id, au_lname, au_fname, phone
from authors 
go
```
Note that this view must contain the primary key of the underlying table (in this case, au_id).

Create a table creation script that will create a table on the subscriber containing the data columns that you want to pass.

For example, construct a script named PHONELIST.SCH:

EXEC sp_addtype id, 'varchar ( 11 ) '    
/* adds a user defined datatype */
go
create table phonelist (
au_id id NOT NULL,
  PRIMARY KEY,
au_lname varchar ( 40 ) NOT NULL,
au_fname varchar ( 20 ) NOT NULL,
phone char ( 12 ) NOT NULL
)
go

Add the article to the appropriate publication. For example:

sp_addarticle authors_publication,phonelist,authors, 
@creation_script = '\\WOLFHOUND\PUBLIC\phonelist.sch',
@sync_object = phonelist_view,
@dest_tab = phonelist,
@vertical_partition = 'true',
@description = "Vertical partition of Authors containing id, last name, first name, and phone number "
@type = 5
go

Note that the parameter vertical_partition was passed as 'true' to this stored procedure.

Define the columns that will be published. For example:

sp_articlecolumn authors_publication,phonelist,au_id,add
go
exec sp_articlecolumn authors_publication,phonelist,au_lname,add
go
exec sp_articlecolumn authors_publication,phonelist,au_fname,add
go
exec sp_articlecolumn authors_publication,phonelist,phone,add
go

Optionally, the results can be checked by using sp_helparticle and sp_helparticlecolumns. For example:

sp_helparticle authors_publication, phonelist
go
sp_helparticlecolumns authors_publication, phonelist
go

The preceding example creates a vertically partitioned table on all subscribers, one that is automatically updated whenever the underlying data stored in the authors table of the publication server is modified.