Subqueries that you define on merge articles are not re-evaluated

View products that this article applies to.

This article was previously published under Q324362

Expand all | Collapse all

SYMPTOMS

When you use subqueries to filter data in a merge publication, the subquery is not re-evaluated after the initial synchronization, which may generate unexpected results at the Subscriber.

Back to the top

This behavior occurs because the query is not re-evaluated and the row is not propagated as part of replication when you update a row in a table that is referenced by a subquery. Although you can place a subquery in a row filter, it is not a join filter. Although you can define a subquery that is based on data from another table, this too can cause unexpected results at the Subscriber.

Back to the top

RESOLUTION

To resolve this behavior, use a join filter when you want the filter definition to be re-evaluated during every merge synchronization process.

Back to the top

STATUS

This behavior is by design.

Back to the top

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'Customers', 
 ... 
@subset_filterclause = N'state = ''WA''', 
 ... 


exec sp_addmergearticle    @publication = N'Northwind', @article = N'exec sp_addmergearticle @publication = N'Northwind', @article = N'Orders', 
 ... 
@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.

Back to the top

REFERENCES

The following excerpt is under the "Join Filters" topic in SQL Server Books Online:

Although you can put a subquery into a row filter, it is not a join filter. If you update a row in a table referenced by a subquery, the query will not be re-evaluated and the row will not be propagated as part of replication. Replication join filters exist only for merge replication.

The following excerpt is under the "Filter Table Rows" topic in SQL Server Books Online:

You can filter the rows in a table to reduce the amount of data being published. The filter statement must refer only to columns and values in that table. Filters cannot refer to data in other tables to determine whether rows of this table are included or excluded.

Back to the top