Row Filters

Using row filters, you can specify a subset of rows from a table to be published. Row filters can be used when only specific rows need to be propagated to Subscribers, to eliminate rows that users do not need to see (such as rows that contain sensitive or confidential information), or to create different partitions of data that are sent to different Subscribers. For those applications that can, publishing different partitions of data to different Subscribers can also help avoid conflicts that would otherwise be caused by multiple Subscribers updating the same data values.

Row filtering is convenient because it can be applied to existing applications where a site-specific attribute is present to filter on either in the table to be published or in one of its related tables.

In this diagram, the published table is filtered so that only rows 2, 3, and 6 are included in the publication sent to the Subscriber.

Row filters are available with snapshot replication, transactional replication, and merge replication. Row filters in transactional publications may add significant overhead because the article filter clause is evaluated for each log row written for a published table to determine whether it should be marked for replication. Row filters in transactional publications should be avoided where each site can support the full data load, the overall data set is reasonably small, and the number of insert, update, and delete transactions per day is low.

Row filters in snapshot replication and transactional replication are static and the WHERE clause criteria you set in the Create Publication Wizard or the publication properties dialog box stays the same until you modify it. If you had two Subscribers that require different rows of data from the publishing table, you would need two different publications each with a different row filter to retrieve the correct rows for each Subscriber.

Although you can put a subquery into a row filter, it is not a join filter. If you update a row in a table referenced by a subquery, the query will not be re-evaluated and the row will not be propagated as part of replication. Replication join filters exist only for merge replication. For more information, see Join Filters.

An alternative to creating multiple publications is to use a dynamic filter for merge replication or create a transformable subscription with a custom filter for snapshot replication or transactional replication that dynamically creates data partitions based on information from individual Subscribers. For more information, see Dynamic Filters and Transforming Published Data.

Example

Northwind Traders, Inc. needs to publish customer and orders information to its sales staff. The sales representatives are assigned to service customers based on the region where the customer is located, and Northwind wants the sales representatives to have access only to the data for their specified region.

Because the sales representatives need to update the data frequently and make updates while connected and while disconnected from the Publisher, the replication administrator at Northwind decides to use merge replication and create different publications with row filters based on region. The publication would include data from the customers, orders, and order details table. For example, one of the publications would be restricted for the Northwest region of the United States. In the Specify Filter dialog box in the Create Publication Wizard, the WHERE clause would read:

SELECT <published_columns> FROM [dbo].[Customers] WHERE Region = 'WA'

Because data partitions based on region will be sent to Subscribers that have exclusive, logical ownership of each region, conflicts that could occur when multiple Subscribers update the same data will be avoided. However, conflicts may still occur if the Publisher and Subscriber update the same data. For more information, see Merge Replication and Merge Replication Conflict Detection and Resolution.

An alternate, often preferable approach to this type of situation is to use a dynamic filter for a merge publication or a transformable subscription for a snapshot or transactional publication. For more information, see Dynamic Filters and Transforming Published Data.

To filter publications horizontally

Enterprise Manager

SQL-DMO

To filter publications horizontally using publication properties

Enterprise Manager