Column Filters

Column filters restrict the columns to be included as part of a snapshot, transactional, or merge publication. Column filters can reduce the time it takes to propagate data updates to Subscribers, reduce the storage space needed at the Subscriber, and limit the data in a publication to data that is needed by individual Subscribers.

This illustration shows a publication that has a column filter to restrict all columns except columns A, B, and D.

You can also use row and column filtering together, as illustrated here.

When you add a column to a vertical partition, the table structure changes and any INSERT statements on the publishing table will require column lists.

Columns that cannot be vertically filtered from a publication are:

• Columns with primary key constraints.
  
  
• Non-null columns without a default.
  
  
• Columns included in a unique index.
  
  
• The ROWGUID column for merge publications and the ROWGUID column for snapshot or transactional publications that allow immediate updating subscriptions.

For snapshot replication and transactional replication, you can use transformable subscriptions to create custom filters that produce different vertical partitions for different Subscribers using one publication. For more information, see Using Transformable Subscriptions to Create Custom Data Partitions.

Note  If the snapshot or transactional publication allows updatable subscriptions and the publication has a column filter, you cannot filter non-nullable columns without defaults from the publication.

Example

Currently, the sales information is distributed to all sales representatives, but Northwind managers do not want the sales representatives to see the commission amounts paid. The replication administrator can use a column filter to exclude the COMMISSION column from the publication.