Publish only some rows in a table

To create a publication that contains only some of the rows in a table, you must write a search condition that matches only the rows you want to publish. Use of one of the following clauses in your search condition:

SUBSCRIBE BY clause Use the SUBSCRIBE BY clause when multiple subscribers to a publication receive different rows from a table.
The SUBSCRIBE BY clause is recommended when your SQL Remote system requires a large number of subscriptions. The SUBSCRIBE BY clause allows many subscriptions to be associated with a single publication, whereas the WHERE clause does not. Subscribers receive rows depending on the value of a supplied expression.

Publications using a SUBSCRIBE BY clause are more compact, easier to understand, and provide better performance than maintaining several WHERE clause publications.

See Publish only some rows using the SUBSCRIBE BY clause.
WHERE clause Use a WHERE clause to include a subset of rows in an article. All subscribers to the publication containing this article receive the rows that satisfy the WHERE clause.
All unpublished rows must have a default value. Otherwise, when the remote database tries to insert new rows from the consolidated database, an error occurs.

You can combine a WHERE clause in an article.

The database server must add information to the transaction log, and scan the transaction log to send messages, in direct proportion to the number of publications. The WHERE clause does not allow many subscriptions to be associated with a single publication; however the SUBSCRIBE BY clause does.

See Publish only some rows using a WHERE clause.

Example

You need a publication that enables each sales representative to:

Subscribe to their sales orders.
Update their sales orders locally.
Replicate their sales to the consolidated database.

If you use the WHERE clause, you would need to create separate publications for each sales representative. The following publication is for a sales representative named Sam Singer; each of the other sales representatives would need a similar publication.

CREATE PUBLICATION PubOrdersSamSinger (
   TABLE SalesOrders
      WHERE Active = 1
);

The following statement subscribes Sam Singer to the PubsOrdersSamSinger publication.

CREATE SUBSCRIPTION
TO PubOrdersSamSinger
FOR Sam_Singer;

If you use the SUBSCRIBE BY clause, you need only one publication. All of the sales representatives can use the following publication:

CREATE PUBLICATION PubOrders (
   TABLE SalesOrders 
   SUBSCRIBE BY SalesRepresentativeID
);

The following statement subscribes Sam Singer to the PubsOrders publication by his ID, 8887.

CREATE SUBSCRIPTION
TO PubOrders ('8887')
FOR Sam_Singer;

Publish only some rows using the SUBSCRIBE BY clause
Publish only some rows using a WHERE clause

Discuss this page in DocCommentXchange.