MORE INFORMATION
A fundamental design goal for merge replication is to
partition the data in such a way that each Subscriber receives the smallest
data set possible. One technique to implement this is to define subset filter
clauses. With a subset filter clause, which is an article property, you can
define a logical expression. You can use this logical expression in the WHERE
clause of queries to evaluate data that is to be replicated to the subscribing
tables. You can also include a subquery in the subset filter clauses.
For example, a
Customers table at the Publisher contains customer data for several states,
and an
Orders table tracks product orders. Consider the following initial data
at the Publisher:
Customers:
CustomerID State
1 WA
2 TX
3 WA
Orders:
OrderID CustomerID
1 1
2 1
3 3
4 2
The business logic of the application requires that the Publisher only
sent orders that belong to customers from the state of Washington (WA) to the
Subscribers. Also, the
Customers table is not filtered. To implement this logic, you can define
the following subset filter clause that uses a subquery on the
Orders table:
exec sp_addmergearticle @publication = N'Northwind', @article = N'Orders',
...
@subset_filterclause = N'orders.customerid in ( select customerid from customers where state = ''wa'' )',
...
exec sp_addmergearticle @publication = N'Northwind', @article = N'Customers',
...
@subset_filterclause = null,
...
Note The subquery in the preceding filter statements is the following
SQL statement:
select customerid from customers where state = ''wa''
Although you can successfully specify the subset filter clause, this
violates the requirement for row filters to refer only to columns and to values
in that table.
When this publication is synchronized the initial
synchronization process honors the preceding subquery and therefore the
following data is sent to the Subscriber:
Customers:
CustomerID State
1 WA
2 TX
3 WA
Orders:
OrderID CustomerID
1 1
2 1
3 3
For example, by making the following update at the Publisher
update customers set state = 'VA' where customerID = 1
this results in the following data at the Publisher:
Customers:
CustomerID State
1 VA
2 TX
3 WA
Orders:
OrderID CustomerID
1 1
2 1
3 3
4 2
Start the merge synchronization process to generate the following data
results at the Subscriber:
Customers:
CustomerID State
1 VA
2 TX
3 WA
Orders:
OrderID CustomerID
1 1
2 1
3 3
The
Orders table still includes orderID 1 and 2, which no longer belong to
customers from Washington State. This is because the subquery that you defined
on the
Orders table is not re-evaluated. To avoid this problem, the
Customers table must be filtered on the
state column and you must definite a join filter clause on the
Customers and
Orders tables. For example:
exec sp_addmergearticle @publication = N'Northwind', @article = N'<?xm-deletion_mark author="v-ericz" time="20060214T153927+0800" data="Orders"?><?xm-insertion_mark_start author="v-ericz" time="20060214T153924+0800"?>Customers<?xm-insertion_mark_end?>',
...
@subset_filterclause = N'state = ''WA''',
...
exec sp_addmergearticle @publication = N'Northwind', @article = N'<?xm-deletion_mark author="v-ericz" time="20060214T153935+0800" data="Customers"?><?xm-insertion_mark_start author="v-ericz" time="20060214T153935+0800"?>Orders<?xm-insertion_mark_end?>',
...
@subset_filterclause = null,
...
exec sp_addmergefilter @publication = N'Northwind', @article = N'orders', @filtername = N'orders_customers', @join_articlename = N'customers', @join_filterclause = N'customers.customerid = orders.customerid', @join_unique_key = 0
GO
Note In this example, you must filter the
Customers table.