How to: Define and Modify a Static Row Filter (Replication Transact-SQL Programming)
When creating table articles, you can define a WHERE clause to filter rows out of an article. You can also change a row filter after it has been defined. Static row filters can be created and modified programmatically using replication stored procedures. Because these filters are static, all subscribers will receive the same subset of the data. If you need to dynamically filter rows in a table article belonging to a merge publication so that each subscriber receives a different partition of the data, see How to: Define and Modify a Parameterized Row Filter for a Merge Article (Replication Transact-SQL Programming). Merge replication also enables you to filter related rows based on an existing row filter. For more information, see How to: Define and Modify a Join Filter Between Merge Articles (Replication Transact-SQL Programming).
To define a static row filter for a snapshot or transactional publication
Define the article to filter. For more information, see How to: Define an Article (Replication Transact-SQL Programming).
At the Publisher on the publication database, execute sp_articlefilter (Transact-SQL). Specify the name of the article for @article, the name of the publication for @publication, a name for the filter for @filter_name, and the filtering clause for @filter_clause (not including WHERE).
If a column filter must still be defined, see How to: Define and Modify a Column Filter (Replication Transact-SQL Programming). Otherwise, execute sp_articleview (Transact-SQL). Specify the publication name for @publication, the name of the filtered article for @article, and the filter clause specified in step 2 for @filter_clause. This creates the synchronization objects for the filtered article.
To modify a static row filter for a snapshot or transactional publication
At the Publisher on the publication database, execute sp_articlefilter (Transact-SQL). Specify the name of the article for @article, the name of the publication for @publication, a name for the new filter for @filter_name, and the new filtering clause for @filter_clause (not including WHERE). Because this change will invalidate data in existing subscriptions, specify a value of 1 for @force_reinit_subscription.
At the Publisher on the publication database, execute sp_articleview (Transact-SQL). Specify the publication name for @publication, the name of the filtered article for @article, and the filter clause specified in step 1 for @filter_clause. This re-creates the view that defines the filtered article.
Rerun the Snapshot Agent job for the publication to generate an updated snapshot. For more information, see How to: Create the Initial Snapshot (Replication Transact-SQL Programming).
Reinitialize subscriptions. For more information, see How to: Reinitialize a Subscription (Replication Transact-SQL Programming).
To delete a static row filter for a snapshot or transactional publication
At the Publisher on the publication database, execute sp_articlefilter (Transact-SQL). Specify the name of the article for @article, the name of the publication for @publication, a value of NULL for @filter_name, and a value of NULL for @filter_clause. Because this change will invalidate data in existing subscriptions, specify a value of 1 for @force_reinit_subscription.
Rerun the Snapshot Agent job for the publication to generate an updated snapshot. For more information, see How to: Create the Initial Snapshot (Replication Transact-SQL Programming).
Reinitialize subscriptions. For more information, see How to: Reinitialize a Subscription (Replication Transact-SQL Programming).
To define a static row filter for a merge publication
At the Publisher on the publication database, execute sp_addmergearticle (Transact-SQL). Specify the filtering clause for @subset_filterclause (not including WHERE). For more information, see How to: Define an Article (Replication Transact-SQL Programming).
If a column filter must still be defined, see How to: Define and Modify a Column Filter (Replication Transact-SQL Programming).
To modify a static row filter for a merge publication
At the Publisher on the publication database, execute sp_changemergearticle (Transact-SQL). Specify the publication name for @publication, the name of the filtered article for @article, a value of subset_filterclause for @property, and the new filtering clause for @value (not including WHERE). Because this change will invalidate data in existing subscriptions, specify a value of 1 for @force_reinit_subscription.
Rerun the Snapshot Agent job for the publication to generate an updated snapshot. For more information, see How to: Create the Initial Snapshot (Replication Transact-SQL Programming).
Reinitialize subscriptions. For more information, see How to: Reinitialize a Subscription (Replication Transact-SQL Programming).
Example
In this transactional replication example, the article is filtered horizontally to remove all discontinued products.
DECLARE @publication AS sysname;
DECLARE @table AS sysname;
DECLARE @filterclause AS nvarchar(500);
DECLARE @filtername AS nvarchar(386);
DECLARE @schemaowner AS sysname;
SET @publication = N'AdvWorksProductTran';
SET @table = N'Product';
SET @filterclause = N'[DiscontinuedDate] IS NULL';
SET @filtername = N'filter_out_discontinued';
SET @schemaowner = N'Production';
-- Add a horizontally and vertically filtered article for the Product table.
-- Manually set @schema_option to ensure that the Production schema
-- is generated at the Subscriber (0x8000000).
EXEC sp_addarticle
@publication = @publication,
@article = @table,
@source_object = @table,
@source_owner = @schemaowner,
@schema_option = 0x80030F3,
@vertical_partition = N'true',
@type = N'logbased',
@filter_clause = @filterclause;
-- (Optional) Manually call the stored procedure to create the
-- horizontal filtering stored procedure. Since the type is
-- 'logbased', this stored procedures is executed automatically.
EXEC sp_articlefilter
@publication = @publication,
@article = @table,
@filter_clause = @filterclause,
@filter_name = @filtername;
-- Add all columns to the article.
EXEC sp_articlecolumn
@publication = @publication,
@article = @table;
-- Remove the DaysToManufacture column from the article
EXEC sp_articlecolumn
@publication = @publication,
@article = @table,
@column = N'DaysToManufacture',
@operation = N'drop';
-- (Optional) Manually call the stored procedure to create the
-- vertical filtering view. Since the type is 'logbased',
-- this stored procedures is executed automatically.
EXEC sp_articleview
@publication = @publication,
@article = @table,
@filter_clause = @filterclause;
GO
In this merge replication example, the articles are filtered horizontally to return only rows that belong to the specified salesperson. A join filter is also used. For more information, see How to: Define and Modify a Join Filter Between Merge Articles (Replication Transact-SQL Programming).
DECLARE @publication AS sysname;
DECLARE @table1 AS sysname;
DECLARE @table2 AS sysname;
DECLARE @table3 AS sysname;
DECLARE @salesschema AS sysname;
DECLARE @hrschema AS sysname;
DECLARE @filterclause AS nvarchar(1000);
SET @publication = N'AdvWorksSalesOrdersMerge';
SET @table1 = N'Employee';
SET @table2 = N'SalesOrderHeader';
SET @table3 = N'SalesOrderDetail';
SET @salesschema = N'Sales';
SET @hrschema = N'HumanResources';
SET @filterclause = N'Employee.LoginID = HOST_NAME()';
-- Add a filtered article for the Employee table.
EXEC sp_addmergearticle
@publication = @publication,
@article = @table1,
@source_object = @table1,
@type = N'table',
@source_owner = @hrschema,
@schema_option = 0x0004CF1,
@description = N'article for the Employee table',
@subset_filterclause = @filterclause;
-- Add an article for the SalesOrderHeader table that is filtered
-- based on Employee and horizontally filtered.
EXEC sp_addmergearticle
@publication = @publication,
@article = @table2,
@source_object = @table2,
@type = N'table',
@source_owner = @salesschema,
@vertical_partition = N'true',
@schema_option = 0x0034EF1,
@description = N'article for the SalesOrderDetail table';
-- Add an article for the SalesOrderDetail table that is filtered
-- based on SaledOrderHeader.
EXEC sp_addmergearticle
@publication = @publication,
@article = @table3,
@source_object = @table3,
@source_owner = @salesschema,
@description = 'article for the SalesOrderHeader table',
@identityrangemanagementoption = N'auto',
@pub_identity_range = 100000,
@identity_range = 100,
@threshold = 80,
@schema_option = 0x0004EF1;
-- Add all columns to the SalesOrderHeader article.
EXEC sp_mergearticlecolumn
@publication = @publication,
@article = @table2,
@force_invalidate_snapshot = 1,
@force_reinit_subscription = 1;
-- Remove the credit card Approval Code column.
EXEC sp_mergearticlecolumn
@publication = @publication,
@article = @table2,
@column = N'CreditCardApprovalCode',
@operation = N'drop',
@force_invalidate_snapshot = 1,
@force_reinit_subscription = 1;
-- Add a merge join filter between Employee and SalesOrderHeader.
EXEC sp_addmergefilter
@publication = @publication,
@article = @table2,
@filtername = N'SalesOrderHeader_Employee',
@join_articlename = @table1,
@join_filterclause = N'Employee.BusinessEntityID = SalesOrderHeader.SalesPersonID',
@join_unique_key = 1,
@filter_type = 1,
@force_invalidate_snapshot = 1,
@force_reinit_subscription = 1;
-- Add a merge join filter between SalesOrderHeader and SalesOrderDetail.
EXEC sp_addmergefilter
@publication = @publication,
@article = @table3,
@filtername = N'SalesOrderDetail_SalesOrderHeader',
@join_articlename = @table2,
@join_filterclause = N'SalesOrderHeader.SalesOrderID = SalesOrderDetail.SalesOrderID',
@join_unique_key = 1,
@filter_type = 1,
@force_invalidate_snapshot = 1,
@force_reinit_subscription = 1;
GO