Replication

Join Filters

Join filters allow cross table relationships to be used in merge replication filters when the filter of one table is based on another table in the publication. A join filter defines a relationship between two tables that will be enforced during the merge process; it is similar to specifying a join between two tables. The join filter names two articles, and specifies the join condition to represent the relationship between the two tables in the articles. The join condition is usually in the form:

ARTICLE1_TABLE.COLUMN = ARTICLE2_TABLE.COLUMN

Join filters are typically used in conjunction with row filters and allow the merge process to maintain the referential integrity between the two tables. If a table published with a row filter is referenced by a foreign key in another published table, the foreign key table's article must have a join filter to represent the referential dependency on the primary key table article.

SQL Server Enterprise Manager uses this rule when creating a publication to suggest the join filter logic automatically for the foreign key table based in the foreign key reference. For this reason and also for ease of use, it is recommended that you declare the proper primary key to foreign key relationships and then let the join filters be generated automatically when you create a publication using the Create Publication Wizard.

Note  The syntax for creating FOREIGN KEY constraints with CREATE TABLE or ALTER TABLE allows the NOT FOR REPLICATION option. When this option is set, Microsoft® SQL Server™ 2000 assumes that the reference was validated when the user made the data change; therefore, SQL Server 2000 does not perform the extra processing steps to verify the reference when the merge process synchronizes the data. If this option is used, a merge filter must be defined to avoid invalid foreign key rows at the subscriber.

Join filters are not limited strictly to primary key/foreign key relationships. The join filter can be based on any comparison logic that associates the data in the two article tables, but the logic should use indexed columns if possible for best performance.

The merge process has special performance optimizations depending on whether the join condition is based on a unique column, as is the case when the join filter represents a foreign key relationship. If the join condition is based on a unique column, the join_unique_key property should be set for the article for best performance.

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.

Warning  Join filters with several tables (such as dozens or hundreds of tables) will seriously impact performance during merge processing. It is recommended that if you are generating join filters of five or more tables that you consider other solutions. Another strategy might be to not filter tables which are primarily lookup tables, smaller tables, and tables that are not subject to change. Make those tables part of the publication in their entirety. It is recommended that you use join filters only between tables for which it is important they carefully partition among Subscribers.

Example

The Northwind Traders database contains a CUSTOMERS table and an ORDERS table. The CUSTOMERS table has a CustomerID primary key, and the ORDERS table has a CustomerID column with a foreign key constraint that relates to the CustomerID column in the CUSTOMERS table. You can also add a new column to the CUSTOMERS table titled Status to show whether the customer is active or inactive.

The CUSTOMERS table

CustomerID CustomerName Status
ALFKI Alfreds Futterkiste Active
ANATR Ana Trujillo Emparedados. . . Inactive
ANTON Antonio Moreno Taqueria Active

The ORDERS table

OrderID CustomerID OrderDate
10643 ALFKI 1997-08-25
11077 RATTC 1998-05-06
10926 ANATR 1998-03-04
11000 RATTC 1998-04-06
11010 REGGC 1998-04-09
10569 RATTC 1997-06-16

The join filter for these tables would be defined for the ORDERS article. The join article would be the CUSTOMERS article, and the join filter clause would be:

CUSTOMERS.CUSTOMERID=ORDERS.CUSTOMERID

If the CUSTOMERS table article in the publication has a row filter clause of Status = 'Active', the merge process publishes only the Alfreds Futterkiste and Antonio Moreno Taqueria customer data to the Subscriber.

If no join filter is present to restrict the ORDERS table data to the filtered customers, the merge process fails with a primary key violation for the CustomerID column in the ORDERS table. This is because the process attempts to insert the inactive customers' transaction rows that have no valid CustomerID in the CUSTOMERS table at the Subscriber.

The ORDERS table data with no join filter applied to the Subscriber.

OrderID CustomerID OrderDate
10643 ALFKI 1997-08-25
11077* RATTC 1998-05-06
10926 ANATR 1998-03-04
11000* RATTC 1998-04-06
11010* REGGC 1998-04-09
10569* RATTC 1997-06-16

*These rows violate the foreign key on the CustomerID column at the Subscriber.

To avoid this problem, add a join filter to the ORDERS table that represents the referential dependence on the CUSTOMERS table. The merge process replicates only the ORDERS data for the active customers.

The CUSTOMERS table at the Publisher.

CustomerID CustomerName Status
ALFKI Alfreds Futterkiste Active
ANATR Ana Trujillo Emparedados. . . Inactive
ANTON Antonio Moreno Taqueria Active

The ORDERS table at the Publisher.

OrderID CustomerID OrderDate
10643 ALFKI 1997-08-25
11077 RATTC 1998-05-06
10926 ANATR 1998-03-04
11000 RATTC 1998-04-06
11010 REGGC 1998-04-09
10569 RATTC 1997-06-16

The CUSTOMERS table at the Subscriber with a row filter clause for Active customers.

CustomerID CustomerName Status
ALFKI Alfreds Futterkiste Active
ANTON Antonio Moreno Taqueria Active

The ORDERS table at the Subscriber with a join filter to Active customers.

OrderID CustomerID OrderDate
10643 ALFKI 1997-08-25
10926 ANTON 1998-03-04