How to: Define and Modify a Static Row Filter (SQL Server Management Studio)

  • Article

Define, modify, and delete static 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). If the publication is enabled for peer-to-peer transactional replication, tables cannot be filtered.

Note

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

  1. On the Filter Table Rows page of the New Publication Wizard or the Filter Rows page of the Publication Properties - <Publication> dialog box, the action you take depends on the type of publication:

    • For a snapshot or transactional publication, click Add.
    • For a merge publication, click Add, and then click Add Filter.

  2. In the Add Filter dialog box, select a table to filter from the drop-down list box.

  3. Create a filter statement in the Filter statement text area. You can type directly in the text area, and you can also drag and drop columns from the Columns list box.

    Note

    The WHERE clause should use two-part naming; three-part naming and four-part naming are not supported. If the publication is from an Oracle Publisher, the WHERE clause must be compliant with Oracle syntax.

    • The Filter statement text area includes the default text, which is in the form of:

      SELECT <published_columns> FROM [schema].[tablename] WHERE

    • The default text cannot be changed; type the filter clause after the WHERE keyword using standard SQL syntax. The complete filter clause would appear like:

      SELECT <published_columns> FROM [HumanResources].[Employee] WHERE [LoginID] = 'adventure-works\ranjit0'

    • A static row filter can include a user-defined function. The complete filter clause for a static row filter with a user-defined function would appear like:

      SELECT <published_columns> FROM [Sales].[SalesOrderHeader] WHERE MyFunction([Freight]) > 100

  4. Click OK.

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

To modify a static row filter

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

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

  3. Click OK.

To delete a static row filter

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

See Also

Tasks

How to: Define and Modify a Parameterized Row Filter for a Merge Article (SQL Server Management Studio)

Concepts

Filtering Published Data

Other Resources

How to: Define and Modify a Static Row Filter (Replication Transact-SQL Programming)

Help and Information

Getting SQL Server 2005 Assistance