Subsetting data in an SQL Replication environment

Replication usually involves subsetting. It might involve the choice of certain columns and rows to replicate from a source table when you register a replication source. It might involve the choice of certain registered columns to replicate to each target table when you create subscription sets.

Depending on your replication requirements, you can subset data at the source during registration or at the target during subscription:

• If you have only one target for a source, or if multiple targets need exactly the same data, then it is possible to subset or manipulate data at registration because you do not need to consider potentially different needs of different targets.
• If you have one source and multiple targets, and the multiple targets have different requirements regarding the data to be applied, then it might not be possible to subset at registration. In this case, you subset data at subscription.

Views are used to subset data at registration time, while query predicates are used to subset data at subscription time. In many situations, it depends on your preference whether to use subscription predicates or registered views. A few factors might influence you:

• Views might already exist and meet the qualifications to be a registered view for replication.
• You might find views to be an easier approach to verify the subsetting that you defined for replication.
• Subscription predicates are stored in replication control tables, which eliminates the need to create and manage views.

Do not use any of these techniques if you are replicating to replica target tables. The master table and replica tables in update-anywhere configurations replicate data back and forth to one another. Replica tables can have a subset of the source table columns as long as the columns that are not used are nullable. Otherwise, replica tables must contain the same columns as the source table so you cannot subset columns, add new columns, or rename columns.