Define and Modify a Join Filter Between Merge Articles

This topic describes how to define and modify a join filter between merge articles in SQL Server 2012 by using SQL Server Management Studio or Transact-SQL. Merge replication supports join filters, which are typically used in conjunction with parameterized filters to extend table partitioning to other related table articles.

In This Topic

• Before you begin:

  Limitations and Restrictions

  Recommendations

• To define and modify a join filter between merge articles, using:

  SQL Server Management Studio

  Transact-SQL

Before You Begin

Limitations and Restrictions

• To create a join filter, a publication must contain at least two related tables. A join filter extends a row filter; therefore you must define a row filter on one table before you can extend the filter with a join to another table. After one join filter is defined, you can extend this join filter with another join filter if the publication contains additional related tables.

• If you add, modify, or delete a join filter after subscriptions to the publication have been initialized, you must generate a new snapshot and reinitialize all subscriptions after making the change. For more information about requirements for property changes, see Change Publication and Article Properties.

Recommendations

• Join filters can be created manually for a set of tables, or replication can generate the filters automatically based on the relationships between foreign keys and primary keys defined on the tables. For more information on generating a set of join filters automatically, see Automatically Generate a Set of Join Filters Between Merge Articles (SQL Server Management Studio).

[Top]

Using SQL Server Management Studio

Define, modify, and delete join filters on the Filter Table Rows page of the New Publication Wizard or the Filter Rows page of the Publication Properties - <Publication> dialog box. For more information about using the wizard and accessing the dialog box, see Create a Publication and View and Modify Publication Properties.

To define a join filter

1. On the Filter Table Rows page of the New Publication Wizard or the Filter Rows page of the Publication Properties - <Publication>, select an existing row filter or join filter in the Filtered Tables pane.

2. Click Add, and then click Add Join to Extend the Selected Filter.

3. Create the join statement: select either Use the builder to create the statement or Write the join the statement manually.

   • If you select to use the builder, use the columns in the grid (Conjunction, Filtered table column, Operator, and Joined table column) to build a join statement.

     Each column in the grid contains a drop-down combo box, allowing you to select two columns and an operator (=, <>, <=, <, >=, >, and like). The results are displayed in the Preview text area. If the join involves more than one pair of columns, select a conjunction (AND or OR) from the Conjunction column, and then enter two more columns and an operator.

   • If you select to write the statement manually, write the join statement in the Join statement text area. Use the Filtered table columns list box and the Joined table columns list box to drag and drop columns to the Join statement text area.

   • The complete join statement would appear like:

     SELECT <published_columns> FROM [Sales].[SalesOrderHeader] INNER JOIN [Sales].[SalesOrderDetail] ON [SalesOrderHeader].[SalesOrderID] = [SalesOrderDetail].[SalesOrderID]
     

     The JOIN clause should use two-part naming; three-part naming and four-part naming are not supported.

4. Specify join options:

   • If the column on which you join in the filtered table (the parent table) is unique, select Unique key.

     Warning

     Selecting this option indicates that the relationship between the child and parent tables in a join filter is one to one or one to many. Only select this option if you have a constraint on the joining column in the child table that guarantees uniqueness. If the option is set incorrectly, non-convergence of data can occur.

   • By default, merge replication processes changes on a row-by-row basis during synchronization. To have related changes in rows of both the filtered table and the joined table processed as a unit, select Logical record (Microsoft SQL Server 2005 and later versions only). This option is available only if the article and publication requirements for using logical records are met. For more information see the section "Considerations for Using Logical Records" in Group Changes to Related Rows with Logical Records.

5. Click OK.  

6. If you are in the Publication Properties - <Publication> dialog box, click OK to save and close the dialog box.

To modify a join filter

1. On the Filter Table Rows page of the New Publication Wizard or the Filter Rows page of the Publication Properties - <Publication>, select a filter in the Filtered Tables pane, and then click Edit.

2. In the Edit Join dialog box, modify the filter.

3. Click OK.  

To delete a join filter

• On the Filter Table Rows page of the New Publication Wizard or the Filter Rows page of the Publication Properties - <Publication>, select a filter in the Filtered Tables pane, and then click Delete. If the join filter you delete is itself extended by other joins, those joins will also be deleted.

[Top]

Using Transact-SQL

These procedures show a parameterized filter on a parent article with join filters between this article and related child articles. Join filters can be defined and modified programmatically using replication stored procedures.

To define a join filter to extend an article filter to related articles in a merge publication

1. Define the filtering for the article being joined to, which is also known as the parent article.

   • For an article filtered using a parameterized row filter, see Define and Modify a Parameterized Row Filter for a Merge Article.

   • For an article filtered using a static row filter, see Define and Modify a Static Row Filter.

2. At the Publisher on the publication database, execute sp_addmergearticle (Transact-SQL) to define one or more related articles, which are also known as child articles, for the publication. For more information, see Define an Article.

3. At the Publisher on the publication database, execute sp_addmergefilter (Transact-SQL). Specify @publication, a unique name for this filter for @filtername, the name of the child article created in step 2 for @article, the name of the parent article being joined to for @join_articlename, and one of the following values for @join_unique_key:

   • 0 - indicates a many-to-one or many-to-many join between the parent and child articles.

   • 1 - indicates a one-to-one or one-to-many join between the parent and child articles.

   This defines a join filter between the two articles.

   Warning

   Only set @join_unique_key to 1 if you have a constraint on the joining column in the underlying table for the parent article that guarantees uniqueness. If @join_unique_key is set to 1 incorrectly, non-convergence of data may occur.

Examples (Transact-SQL)

This example defines an article for a merge publication, where the SalesOrderDetail table article is filtered against the SalesOrderHeader table that is itself filtered using a static row filter. For more information, see Define and Modify a Static Row Filter.

DECLARE @publication AS sysname;
DECLARE @table1 AS sysname;
DECLARE @table2 AS sysname;
DECLARE @table3 AS sysname;
DECLARE @salesschema AS sysname;
DECLARE @hrschema AS sysname;
DECLARE @filterclause AS nvarchar(1000);
SET @publication = N'AdvWorksSalesOrdersMerge'; 
SET @table1 = N'Employee'; 
SET @table2 = N'SalesOrderHeader'; 
SET @table3 = N'SalesOrderDetail'; 
SET @salesschema = N'Sales';
SET @hrschema = N'HumanResources';
SET @filterclause = N'Employee.LoginID = HOST_NAME()';

-- Add a filtered article for the Employee table.
EXEC sp_addmergearticle 
  @publication = @publication, 
  @article = @table1, 
  @source_object = @table1, 
  @type = N'table', 
  @source_owner = @hrschema,
  @schema_option = 0x0004CF1,
  @description = N'article for the Employee table',
  @subset_filterclause = @filterclause;

-- Add an article for the SalesOrderHeader table that is filtered
-- based on Employee and horizontally filtered.
EXEC sp_addmergearticle 
  @publication = @publication, 
  @article = @table2, 
  @source_object = @table2, 
  @type = N'table', 
  @source_owner = @salesschema, 
  @vertical_partition = N'true',
  @schema_option = 0x0034EF1,
  @description = N'article for the SalesOrderDetail table';

-- Add an article for the SalesOrderDetail table that is filtered
-- based on SaledOrderHeader.
EXEC sp_addmergearticle 
  @publication = @publication, 
  @article = @table3, 
  @source_object = @table3, 
  @source_owner = @salesschema,
  @description = 'article for the SalesOrderHeader table', 
  @identityrangemanagementoption = N'auto', 
  @pub_identity_range = 100000, 
  @identity_range = 100, 
  @threshold = 80,
  @schema_option = 0x0004EF1;

-- Add all columns to the SalesOrderHeader article.
EXEC sp_mergearticlecolumn 
  @publication = @publication, 
  @article = @table2, 
  @force_invalidate_snapshot = 1, 
  @force_reinit_subscription = 1;

-- Remove the credit card Approval Code column.
EXEC sp_mergearticlecolumn 
  @publication = @publication, 
  @article = @table2, 
  @column = N'CreditCardApprovalCode', 
  @operation = N'drop', 
  @force_invalidate_snapshot = 1, 
  @force_reinit_subscription = 1;

-- Add a merge join filter between Employee and SalesOrderHeader.
EXEC sp_addmergefilter 
  @publication = @publication, 
  @article = @table2, 
  @filtername = N'SalesOrderHeader_Employee', 
  @join_articlename = @table1, 
  @join_filterclause = N'Employee.BusinessEntityID = SalesOrderHeader.SalesPersonID', 
  @join_unique_key = 1, 
  @filter_type = 1, 
  @force_invalidate_snapshot = 1, 
  @force_reinit_subscription = 1;

-- Add a merge join filter between SalesOrderHeader and SalesOrderDetail.
EXEC sp_addmergefilter 
  @publication = @publication, 
  @article = @table3, 
  @filtername = N'SalesOrderDetail_SalesOrderHeader', 
  @join_articlename = @table2, 
  @join_filterclause = N'SalesOrderHeader.SalesOrderID = SalesOrderDetail.SalesOrderID', 
  @join_unique_key = 1, 
  @filter_type = 1, 
  @force_invalidate_snapshot = 1, 
  @force_reinit_subscription = 1;
GO

This example defines a group of articles in a merge publication where the articles are filtered with a series of join filters against the Employee table that is itself filtered using a parameterized row filter on the value of HOST_NAME in the LoginID column. For more information, see Define and Modify a Parameterized Row Filter for a Merge Article.

-- To avoid storing the login and password in the script file, the value 
-- is passed into SQLCMD as a scripting variable. For information about 
-- how to use scripting variables on the command line and in SQL Server
-- Management Studio, see the "Executing Replication Scripts" section in
-- the topic "Programming Replication Using System Stored Procedures".

--Add a new merge publication.
DECLARE @publicationdb AS sysname;
DECLARE @publication AS sysname;
DECLARE @table1 AS sysname;
DECLARE @table2 AS sysname;
DECLARE @filter AS sysname;
DECLARE @schema_hr AS sysname;
DECLARE @schema_sales AS sysname;

SET @publicationdb = N'AdventureWorks2012';
SET @publication = N'AdvWorksSalesPersonMerge';
SET @table1 = N'Employee';
SET @table2 = N'SalesPerson';
SET @filter = N'SalesPerson_Employee';
SET @schema_hr = N'HumanResources';
SET @schema_sales = N'Sales';

USE [AdventureWorks2012];

-- Enable AdventureWorks2012 for merge replication.
EXEC sp_replicationdboption
  @dbname = @publicationdb,
  @optname = N'merge publish',
  @value = N'true';  

-- Create new merge publication with Subscriber requested snapshot
-- and using the default agent schedule. 
EXEC sp_addmergepublication 
  @publication = @publication, 
  @description = N'Merge publication of AdventureWorks2012.', 
  @allow_subscriber_initiated_snapshot = N'true',
  @publication_compatibility_level = N'90RTM';

-- Create a new snapshot job for the publication, using the default schedule.
-- Pass credentials at runtime using sqlcmd scripting variables.
EXEC sp_addpublication_snapshot 
  @publication = @publication, 
  @job_login = $(login), 
  @job_password = $(password);

-- Add an article for the Employee table, 
-- which is horizontally partitioned using 
-- a parameterized row filter.
EXEC sp_addmergearticle 
  @publication = @publication, 
  @article = @table1, 
  @source_owner = @schema_hr, 
  @source_object = @table1, 
  @type = N'table', 
  @description = 'contains employee information', 
  @subset_filterclause = N'[LoginID] = HOST_NAME()';

-- Add an article for the SalesPerson table, 
-- which is partitioned based on a join filter.
EXEC sp_addmergearticle 
  @publication = @publication, 
  @article = @table2, 
  @source_owner = @schema_sales, 
  @source_object = @table2, 
  @type = N'table', 
  @description = 'contains salesperson information';

-- Add a join filter between the two articles.
EXEC sp_addmergefilter 
  @publication = @publication, 
  @article = @table1, 
  @filtername = @filter, 
  @join_articlename = @table2, 
  @join_filterclause = N'[Employee].[BusinessEntityID] = [SalesPerson].[SalesPersonID]', 
  @join_unique_key = 1, 
  @filter_type = 1;
GO

-- Start the agent job to generate the full snapshot for the publication.
-- The filtered data snapshot is generated automatically the first time 
-- the subscription is synchronized. 
DECLARE @publication AS sysname;
SET @publication = N'AdvWorksSalesPersonMerge';

EXEC sp_startpublication_snapshot 
  @publication = @publication;
GO

[Top]

See Also

Concepts

Join Filters

Parameterized Row Filters

Change Publication and Article Properties

Filter Published Data for Merge Replication

Define and Modify a Join Filter Between Merge Articles

Replication System Stored Procedures Concepts

Define a Logical Record Relationship Between Merge Table Articles

Define and Modify a Parameterized Row Filter for a Merge Article