Define and Modify a Static Row Filter

This topic describes how to define and modify a static row filter in SQL Server 2012 by using SQL Server Management Studio or Transact-SQL.

In This Topic

• Before you begin:

  Limitations and Restrictions

  Recommendations

• To define and modify a static row filter, using:

  SQL Server Management Studio

  Transact-SQL

Before You Begin

Limitations and Restrictions

• If you add, modify, or delete a static row filter 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 Change Publication and Article Properties.

• If the publication is enabled for peer-to-peer transactional replication, tables cannot be filtered.

Recommendations

• 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 Define and Modify a Parameterized Row Filter for a Merge Article. Merge replication also enables you to filter related rows based on an existing row filter. For more information, see Define and Modify a Join Filter Between Merge Articles.

[Top]

Using SQL Server Management Studio

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 Create a Publication and View and Modify Publication 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

• 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.

[Top]

Using Transact-SQL

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.

To define a static row filter for a snapshot or transactional publication

1. Define the article to filter. For more information, see Define an Article.

2. 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).

3. If a column filter must still be defined, see Define and Modify a Column Filter. 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

1. 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.

2. 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.

3. Rerun the Snapshot Agent job for the publication to generate an updated snapshot. For more information, see Create and Apply the Initial Snapshot.

4. Reinitialize subscriptions. For more information, see Reinitialize Subscriptions.

To delete a static row filter for a snapshot or transactional publication

1. 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.

2. Rerun the Snapshot Agent job for the publication to generate an updated snapshot. For more information, see Create and Apply the Initial Snapshot.

3. Reinitialize subscriptions. For more information, see Reinitialize Subscriptions.

To define a static row filter for a merge publication

1. 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 Define an Article.

2. If a column filter must still be defined, see Define and Modify a Column Filter.

To modify a static row filter for a merge publication

1. 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.

2. Rerun the Snapshot Agent job for the publication to generate an updated snapshot. For more information, see Create and Apply the Initial Snapshot.

3. Reinitialize subscriptions. For more information, see Reinitialize Subscriptions.

Examples (Transact-SQL)

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 Define and Modify a Join Filter Between Merge Articles.

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

[Top]

See Also

Concepts

Define and Modify a Parameterized Row Filter for a Merge Article

Change Publication and Article Properties

Filter Published Data

Filter Published Data for Merge Replication