Define and Modify a Column Filter

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

In This Topic

• Before you begin:

  Limitations and Restrictions

• To define and modify a column filter, using:

  SQL Server Management Studio

  Transact-SQL

Before You Begin

Limitations and Restrictions

• Some columns cannot be filtered; for more information, see Filter Published Data. If you modify a column filter after subscriptions 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.

[Top]

Using SQL Server Management Studio

Define column filters on the Articles page of the New Publication Wizard. For more information about using the New Publication Wizard, see Create a Publication.

Define and modify column filters on the Articles page of the Publication Properties - <Publication> dialog box. For more information about publication and article properties, see View and Modify Publication Properties.

To define a column filter

1. On the Articles page of the New Publication Wizard, expand the table to be filtered in the Objects to publish pane.

2. Clear the check box next to each column you want to filter.

To modify column filtering

1. On the Articles page of the Publication Properties - <Publication> dialog box, expand the table to be filtered in the Objects to publish pane.

2. Clear the check box next to each column you want to filter, and ensure that the check box is selected for each column that should be included in the article.

3. Click OK.  

[Top]

Using Transact-SQL

When creating table articles, you can define which columns to include in the article and change the columns after the article has been defined. You can create and modify filtered columns programmatically using replication stored procedures.

Note

The following procedures assume that the underlying table has not changed. For information on replicating data definition language (DDL) changes to published tables, see Make Schema Changes on Publication Databases.

To define a column filter for an article published in 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_articlecolumn. This defines the columns to include or remove from the article.

   • If publishing only a few columns from a table with many columns, execute sp_articlecolumn once for each column being added. Specify the column name for @column and a value of add for @operation.

   • If publishing most of the columns in a table with many columns, execute sp_articlecolumn, specifying a value of null for @column and a value of add for @operation to add all columns. Then execute sp_articlecolumn, once for each column being excluded, specifying a value of drop for @operation and the excluded column name for @column.

3. At the Publisher on the publication database, execute sp_articleview. Specify the publication name for @publication and the name of the filtered article for @article. This creates the synchronization objects for the filtered article.

To change a column filter to include additional columns for an article published in a snapshot or transactional publication

1. At the Publisher on the publication database, execute sp_articlecolumn once for each column being added. Specify the column name for @column and a value of add for @operation.

2. At the Publisher on the publication database, execute sp_articleview. Specify the publication name for @publication and the name of the filtered article for @article. If the publication has existing subscriptions, specify a value of 1 for @change_active. This re-creates the synchronization objects for the filtered article.

3. Rerun the Snapshot Agent job for the publication to generate an updated snapshot.

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

To change a column filter to remove columns for an article published in a snapshot or transactional publication

1. At the Publisher on the publication database, execute sp_articlecolumn once for each column being removed. Specify the column name for @column and a value of drop for @operation.

2. At the Publisher on the publication database, execute sp_articleview. Specify the publication name for @publication and the name of the filtered article for @article. If the publication has existing subscriptions, specify a value of 1 for @change_active. This re-creates the synchronization objects for the filtered article.

3. Rerun the Snapshot Agent job for the publication to generate an updated snapshot.

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

To define a column filter for an article published in a merge publication

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

2. At the Publisher on the publication database, execute sp_mergearticlecolumn. This defines the columns to include or remove from the article.

   • If publishing only a few columns from a table with many columns, execute sp_mergearticlecolumn once for each column being added. Specify the column name for @column and a value of add for @operation.

   • If publishing most of the columns in a table with many columns, execute sp_mergearticlecolumn, specifying a value of null for @column and a value of add for @operation to add all columns. Then execute sp_mergearticlecolumn, once for each column being excluded, specifying a value of drop for @operation and the excluded column name for @column.

To change a column filter to include additional columns for an article published in a merge publication

1. At the Publisher on the publication database, execute sp_mergearticlecolumn once for each column being added. Specify the column name for @column, a value of add for @operation and a value of 1 for both @force_invalidate_snapshot and @force_reinit_subscription.

2. Rerun the Snapshot Agent job for the publication to generate an updated snapshot.

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

To change a column filter to remove columns for an article published in a merge publication

1. At the Publisher on the publication database, execute sp_mergearticlecolumn once for each column being removed. Specify the column name for @column, a value of drop for @operation and a value of 1 for both @force_invalidate_snapshot and @force_reinit_subscription.

2. Rerun the Snapshot Agent job for the publication to generate an updated snapshot.

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

Example (Transact-SQL)

In this transactional replication example, the DaysToManufacture column is removed from an article based on the Product table.

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 CreditCardApprovalCode column is removed from an article based on the SalesOrderHeader table.

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

Change Publication and Article Properties

Filter Published Data

Filter Published Data for Merge Replication