Manual Horizontal Partitioning

Horizontal partitioning¾the replication of selected rows 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 horizontal partitioning. The filter stored procedure must follow this format:

CREATE PROCEDURE replica_filter FOR REPLICATION
AS

IF sql_statement
RETURN1
ELSE
RETURN 0

Using this procedure format, you could add partitioning to the examples provided in Setting Up Replication Manually.

For example, a horizontally partitioned article that published only the authors who live in California could be added to the authors_publication article. To do this, add the following steps to the replication setup script created in Setting Up Replication Manually.

1. Create the stored procedure used to qualify rows for publication. For example:

   use pubs
   go
   create procedure ca_authors_filter for replication as
   if exists (select state from authors (NOLOCK) where state = 'CA')
   return 1
   else
   return 0
   go

2. Create a view that will be used to bulk copy out the data during synchronization. For example:

   create view ca_authors_view as select * from authors where state ='CA'
   go

3. Add the article to the appropriate publication. For example:

   sp_addarticle authors_publication,ca_authors,authors, 
   @creation_script = '\\WOLFHOUND\PUBLIC\ca_authors.sch',
   @sync_object = ca_authors_view,
   @filter = ca_authors_filter,
   @dest_tab = ca_authors,
   @description = "Horizontal partition of Authors where state = CA"
   @type = 7
   go

Note that in this example, since both articles come from the same source table, it is necessary to define a separate destination table for the article. The creation script in this case is a modified copy of authors.sch with the table name changed to ca_authors.

If desired, the results can be checked using sp_helparticle and sp_helparticlecolumns. For example:

sp_helparticle
go
sp_helparticlecolumns authors_publication, ca_authors
go

Additionally, in the creation script, you may want to take advantage of the NOT FOR REPLICATION constraint. For example, in the CREATE TABLE statement, you may want to add:

CONSTRAINT ca_constraint CHECK NOT FOR REPLICATION (state <> 'CA')

For information about using NOT FOR REPLICATION, see the Microsoft SQL Server Transact-SQL Reference.

When a replication filter is executed, it requests a lock on the published table. This lock can cause a replication deadlock situation. Therefore, when setting up manual horizontal partitioning, create the filter with the optimizer hint NOLOCK on the published table.