Microsoft® SQL Server™ supports static filters in snapshot, transactional, and merge replication and dynamic filters in merge replication. Static filters use a static, literal value to include or exclude data in a subscription. Dynamic filters, in contrast, use an intrinsic function that is evaluated for each subscription. Dynamic filters allow you to reduce the number of publications required to support partitioned data in certain merge replication applications
For example, if you have an order entry application deployed in a mobile computing environment and each Subscriber should see only the orders which he or she has taken, you can filter data dynamically based on the Subscriber’s name or computer. If you use static filters to tailor subscriptions, you need a separate publication for each Subscriber. To track only orders for sales representative “Bob”, create a publication with a filter clause:
order.rep_name = 'bob'
Add a subscription for Bob to this publication. To track only orders for sales representative “Sue”, create another publication with the filter clause:
order.rep_name = 'sue'
Add a subscription for Sue to this publication. You would repeat the process for each sales representative. With a large number of mobile users, the corresponding large number of tailored publications requires increased management of the publication and additional resources to make future changes to the filter.
Instead of using a static filter and multiple publications, you can use a dynamic filter and a single publication. The dynamic filter is similar to the static filter but includes an intrinsic function:
order.rep_name = suser_sname()
With the dynamic filter, all sales representatives can subscribe to the same publication. During synchronization, the Merge Agent uses the specific Subscriber username to connect with the Publisher and transmit only the data specific to that username.
In preparation for using dynamic filters, you also need to change the PublisherLogin parameter used by the Merge Agent for each pull subscription you establish. You can change the PublisherLogin parameter by either directly updating the publisher_login value in MSsubscription_properties or setting the PublisherLogin parameter of the merge control, if the Merge Agent is launched by using the merge Microsoft ActiveX® control.
Note Dynamic filters change the internal mechanism used for applying the initial snapshot of the publication to a Subscriber. Rather than bulk copying the initial data set, it is transferred as bulk inserts from the Publisher during the first merge operation. This is done to avoid the need to execute and maintain discrete snapshots for each Subscriber at the time a subscription is added. Subscribers still process a common initial snapshot to obtain the base schema associated with the publication.