Define and Modify a Parameterized Row Filter for a Merge Article

Article
03/03/2023

Applies to: SQL Server

This topic describes how to define and modify a parameterized row filter in SQL Server by using SQL Server Management Studio or Transact-SQL.

When creating table articles, you can use parameterized row filters. These filters use a WHERE clause to select the appropriate data to be published. Rather than specifying a literal value in the clause (as you do with a static row filter), you specify one or both of the following system functions: SUSER_SNAME and HOST_NAME. For more information, see Parameterized Row Filters.

In This Topic

Before you begin:

Limitations and Restrictions
To define and modify a parameterized row filter, using:

SQL Server Management Studio

Transact-SQL

Before You Begin

Limitations and Restrictions

If you add, modify, or delete a parameterized row 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

For performance reasons, we recommend that you not apply functions to column names in parameterized row filter clauses, such as LEFT([MyColumn]) = SUSER_SNAME(). If you use HOST_NAME in a filter clause and override the HOST_NAME value, it might be necessary to convert data types using CONVERT. For more information about best practices for this case, see the section "Overriding the HOST_NAME() Value" in the topic Parameterized Row Filters.

Using SQL Server Management Studio

Define, modify, and delete parameterized row 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 parameterized row filter

On the Filter Table Rows page of the New Publication Wizard or the Filter Rows page of the Publication Properties - <Publication>, click Add, and then click Add Filter.
In the Add Filter dialog box, select a table to filter from the drop-down list box.
Create a filter statement in the Filter statement text box. You can type directly in the text area, and you can also drag and drop columns from the Columns list box.
- The Filter statement text area includes the default text, which is in the form of:
```
SELECT <published_columns> FROM [tableowner].[tablename] WHERE  
```
- The default text cannot be changed; type the filter clause after the WHERE keyword using standard SQL syntax. A parameterized filter includes a call to the system function HOST_NAME() and/or SUSER_SNAME(), or a user-defined function that references one or both of these functions. The following is an example of a complete filter clause for a parameterized row filter:
```
SELECT <published_columns> FROM [HumanResources].[Employee] WHERE LoginID = SUSER_SNAME()  
```
  The WHERE clause should use two-part naming; three-part naming and four-part naming are not supported.
Select the option that matches how data will be shared among Subscribers:
- A row from this table will go to multiple subscriptions
- A row from this table will go to only one subscription
If you select A row from this table will go to only one subscription, merge replication can optimize performance by storing and processing less metadata. However, you must ensure that the data is partitioned in such a way that a row cannot be replicated to more than one Subscriber. For more information, see the section "Setting 'partition options'" in the topic Parameterized Row Filters.
Select OK.
If you are in the Publication Properties - <Publication> dialog box, click OK to save and close the dialog box.

To modify a parameterized row 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 Edit.
In the Edit Filter dialog box, modify the filter.
Select OK.

To delete a parameterized row 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.

Using Transact-SQL

Parameterized row filters can be created and modified programmatically using replication stored procedures.

To define a parameterized row filter for an article in a merge publication

At the Publisher on the publication database, execute sp_addmergearticle (Transact-SQL). Specify @publication, a name for the article for @article, the table being published for @source_object, the WHERE clause that defines the parameterized filter for @subset_filterclause (not including WHERE), and one of the following values for @partition_options, which describes the type of partitioning that will result from the parameterized row filter:
- 0 - Filtering for the article either is static or does not yield a unique subset of data for each partition (an "overlapping" partition).
- 1 - Resulting partitions are overlapping, and updates made at the Subscriber cannot change the partition to which a row belongs.
- 2 - Filtering for the article yields nonoverlapping partitions, but multiple Subscribers can receive the same partition.
- 3 - Filtering for the article yields nonoverlapping partitions that are unique for each subscription.

To change a parameterized row filter for an article in a merge publication

At the Publisher on the publication database, execute sp_changemergearticle. Specify @publication, @article, a value of subset_filterclause for @property, the expression that defines the parameterized filter for @value (not including WHERE), and a value of 1 for both @force_invalidate_snapshot and @force_reinit_subscription.
If this change results in different partitioning behavior, then execute sp_changemergearticle again. Specify @publication, @article, a value of partition_options for @property, and the most appropriate partitioning option for @value, which can be one of the following:
- 0 - Filtering for the article either is static or does not yield a unique subset of data for each partition (an "overlapping" partition).
- 1 - Resulting partitions are overlapping, and updates made at the Subscriber cannot change the partition to which a row belongs.
- 2 - Filtering for the article yields nonoverlapping partitions, but multiple Subscribers can receive the same partition.
- 3 - Filtering for the article yields nonoverlapping partitions that are unique for each subscription.

Example (Transact-SQL)

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 LoginID column. During synchronization, the value returned by the HOST_NAME function is overridden. For more information, see Overriding the HOST_NAME() Value in the topic Parameterized Row Filters.

-- 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'AdventureWorks2022';
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 [AdventureWorks2022];

-- Enable AdventureWorks2022 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 AdventureWorks2022.', 
  @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