Share via

  • Article

Publication Properties, Filter Rows

The Filter Rows page of the Publication Properties dialog allows you to add, edit, or delete:

  • Apply static row filters to table articles in snapshot, transactional, and merge publications.

  • Apply parameterized row filters to table articles in merge publications.

  • Use join filters to extend filters on merge table articles to related table articles.

For more information about filtering options, see Filtering Published Data.

Note

Adding, editing, or deleting a filter requires a new snapshot for the publication and requires that all subscriptions be reinitialized.

To maximize application performance and reduce the amount of remote storage required, or to restrict the availability of certain data to specific Subscribers, you should publish only the data required. Your publication can include both unfiltered and filtered tables. For example, you could include the complete (unfiltered) table of company products and use row filters to provide a filtered table of customers for a specific region. By filtering published data, you can:

  • Minimize the amount of data sent over the network.

  • Reduce the amount of storage space required at the Subscriber.

  • Customize publications and applications based on individual Subscriber requirements.

  • Avoid or reduce conflicts if Subscribers are updating data, because different data partitions can be sent to different Subscribers (no two Subscribers will be updating the same data values).

  • Avoid transmitting sensitive data. Row filters and column filters can be used to restrict a Subscriber's access to data. For merge replication, there are security considerations if you use a parameterized filter that includes HOST_NAME(). For more information, see the section "Filtering with HOST_NAME()" in Parameterized Row Filters.

Options

  • Filtered Tables
    This pane is populated with filters as you add them to table articles in the publication. Tables with row filters are shown as top-level nodes in the pane. For merge publications, tables to which filtering has been extended through a join filter are shown as child nodes.

  • Add
    Click Add to launch a dialog box that enables you to filter table articles. Clicking Add for a snapshot or transactional publication launches a dialog box immediately. Clicking Add for a merge publication displays three choices: Add Filter; Add Join to Extend the Selected Filter; Automatically Generate Filters.

    • Select Add Filter to launch the Add Filter dialog box. This dialog box allows you to apply row filters to a table article. In the Add Filter dialog box, you could, for example, specify that a table with customer data should only contain data on French customers when it is replicated to Subscribers.

    • Select Add Join to Extend the Selected Filter to launch the Add Join dialog box. The Add Join dialog box allows you to extend a row filter so that it filters data in tables related to the table with the row filter. For example, if a customer table is filtered so that it only contains data on French customers and there is a related table for customer orders, you can define a join between the two tables so that the orders table only includes orders from French customers.

      Note

      This option is available only if you first select the base table of the join in the filter pane.

    • Select Automatically Generate Filters to launch the Generate Filters dialog box. This dialog box allows you to define a row filter on one table in a merge publication that replication automatically extends to other tables that are related through foreign key relationships. For example, a publication could include three tables: a customer table, an orders table (with a foreign key to the customer table), and an order details table (with a foreign key to the orders table). Define a row filter on the customer table, and replication will extend it to the other tables.

      Note

      When filters are automatically generated by replication, any existing filters on the publication are deleted. To include both filters generated automatically and ones specified manually, generate filters first. You can only specify one set of automatically generated filters for each publication.

  • Edit
    Select a row filter or join filter in the filter pane and click Edit to launch the Edit Filter or Edit Join dialog box.

  • Delete
    Select a row filter or join filter in the filter pane and click Delete to delete the filter.

  • Find Table
    Merge publications only. Click Find Table to find a table in a complex filter tree. In a database with complex relationships, a table can be joined to multiple tables, and therefore can appear in more than one place in the filter tree.

    The actual table appears in only one place in the tree, and in other places, the table is represented by a shortcut. A shortcut to a table is only a reference to the table; it does not show the child nodes of the table. A shortcut node is marked with a shortcut arrow, and expanding that node shows the text Click Find Table to see table for <tablename>.

    Select a shortcut node in the pane and click Find Table The pane is expanded and the table is highlighted. If you click Find Table without a shortcut node selected, a Find Table dialog box is launched.

  • Filter
    Contains the Transact-SQL definition for the filter selected in the filter pane.