Edit

Share via

sp_addmergefilter (Transact-SQL)

  • Article

Applies to: SQL Server Azure SQL Managed Instance

Adds a new merge filter to create a partition based on a join with another table. This stored procedure is executed at the Publisher on the publication database.

Transact-SQL syntax conventions

Syntax

sp_addmergefilter
    [ @publication = ] N'publication'
    , [ @article = ] N'article'
    , [ @filtername = ] N'filtername'
    , [ @join_articlename = ] N'join_articlename'
    , [ @join_filterclause = ] N'join_filterclause'
    [ , [ @join_unique_key = ] join_unique_key ]
    [ , [ @force_invalidate_snapshot = ] force_invalidate_snapshot ]
    [ , [ @force_reinit_subscription = ] force_reinit_subscription ]
    [ , [ @filter_type = ] filter_type ]
[ ; ]

Arguments

[ @publication = ] N'publication'

The name of the publication in which the merge filter is being added. @publication is sysname, with no default.

[ @article = ] N'article'

The name of the article on which the merge filter is being added. @article is sysname, with no default.

[ @filtername = ] N'filtername'

The name of the filter. @filtername is a required parameter. @filtername is sysname, with no default.

[ @join_articlename = ] N'join_articlename'

The parent article to which the child article, specified by @article, must be joined using the join clause specified by @join_filterclause, to determine the rows in the child article that meet the filter criterion of the merge filter. @join_articlename is sysname, with no default. The article must be in the publication given by @publication.

[ @join_filterclause = ] N'join_filterclause'

The join clause that must be used to join the child article specified by @articleand parent article specified by @join_article, to determine the rows qualifying the merge filter. @join_filterclause is nvarchar(1000), with no default.

[ @join_unique_key = ] join_unique_key

Specifies if the join between child article @article and parent article @join_article is one-to-many, one-to-one, many-to-one, or many-to-many. @join_unique_key is int, with a default of 0.

  • 0 indicates a many-to-one or many-to-many join.

  • 1 indicates a one-to-one or one-to-many join.

This value is 1 when the joining columns form a unique key in @join_article, or if @join_filterclause is between a foreign key in @article and a primary key in @join_article.

Caution

Only set this parameter to 1 if you've 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 might occur.

[ @force_invalidate_snapshot = ] force_invalidate_snapshot

Acknowledges that the action taken by this stored procedure might invalidate an existing snapshot. @force_invalidate_snapshot is bit, with a default of 0.

  • 0 specifies that changes to the merge article don't cause the snapshot to be invalid. If the stored procedure detects that the change does require a new snapshot, an error occurs and no changes are made.

  • 1 specifies that changes to the merge article might cause the snapshot to be invalid, and if there are existing subscriptions that would require a new snapshot, gives permission for the existing snapshot to be marked as obsolete and a new snapshot generated.

[ @force_reinit_subscription = ] force_reinit_subscription

Acknowledges that the action taken by this stored procedure might require existing subscriptions to be reinitialized. @force_reinit_subscription is bit, with a default of 0.

  • 0 specifies that changes to the merge article don't cause the subscription to be reinitialized. If the stored procedure detects that the change would require subscriptions to be reinitialized, an error occurs and no changes are made.

  • 1 specifies that changes to the merge article cause existing subscriptions to be reinitialized, and gives permission for the subscription reinitialization to occur.

[ @filter_type = ] filter_type

Specifies the type of filter being added. @filter_type is tinyint, and can be one of the following values.

Value Description
1 Join filter only. Required to support SQL Server Compact Subscribers.
2 Logical record relationship only.
3 Both join filter and logical record relationship.

For more information, see Group Changes to Related Rows with Logical Records.

Return code values

0 (success) or 1 (failure).

Remarks

sp_addmergefilter is used in merge replication.

sp_addmergefilter can only be used with table articles. View and indexed view articles aren't supported.

This procedure can also be used to add a logical relationship between two articles that might or might not have a join filter between them. @filter_type is used to specify if the merge filter being added is a join filter, a logical relation, or both.

To use logical records, the publication and articles must meet several requirements. For more information, see Group Changes to Related Rows with Logical Records.

Typically, this option is used for an article that's a foreign key reference to a published primary key table, and the primary key table has a filter defined in its article. The subset of primary key rows is used to determine the foreign key rows that are replicated to the Subscriber.

You can't add a join filter between two published articles when the source tables for both articles share the same table object name. In such a case, even if both tables are owned by different schemas and have unique article names, creation of the join filter fails.

When both a parameterized row filter and a join filter are used on a table article, replication determines whether a row belongs in a Subscriber's partition. It does so by evaluating either the filtering function or the join filter (using the OR operator), rather than evaluating the intersection of the two conditions (using the AND operator).

Examples

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.EmployeeID = 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

Permissions

Only members of the sysadmin fixed server role or db_owner fixed database role can execute sp_addmergefilter.

Related content