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

Northwind Traders is using merge replication to publish customer and orders information to its mobile, occasionally connected sales representatives. The central office decides to track the commission that each sales representative made on an order, and a column named COMMISSION is added to the ORDER DETAILS table.

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.

To filter publications vertically

Enterprise Manager

SQL-DMO

To filter publications vertically using publication properties

Enterprise Manager