How to: Define and Modify a Parameterized Row Filter for a Merge Article (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 How to: Create a Publication and Define Articles (SQL Server Management Studio) and How to: View and Modify Publication and Article Properties (SQL Server Management Studio).
Note
If you add, modify, or delete a parameterized row filter in the Publication Properties - <Publication> dialog box 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 Changing Publication and Article 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] WHEREThe 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.
Important
For performance reasons, we recommended 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.
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.
Click 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.
Click 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.
See Also
Concepts
Join Filters
Parameterized Row Filters