SQL Server Transactional Replication Static Row and Column Filters

By:   |   Comments   |   Related: > Replication


SQL Server Replication is widely used to populate reporting databases or to disperse data geographically. Based on the need to send data to various subscribers with different requirements there may be a need to restrict relevant data either at a column level or row level. How can we filter data when replicated?


Suppose we have a centralized SQL Server OLTP database that stores data from transactions produced across the globe. For reporting purposes we need to segment the data only for a particular location as well as exclude any sensitive data.

So the replication restrictions are based on the following:

  1. Each subscriber should only receive data for their location. So, if we have three subscribers (UK, US and Asia) we want each subscriber to only get their data (i.e. UK only gets UK data).
  2. Customer sensitive data should not be replicated, such as customer name.

We can achieve these requirements using replication filters, so let's see how to do this by creating a sample database.

Create Database OnlineMarket

Use OnlineMarket

Create table dbo.orders(
Orderid int primary key clustered,
ProductName Varchar(10),
OrderQuantity int,
CustomerName Varchar(10),
CustomerAddress varchar(10),
CustomerContactNo	int


insert into dbo.orders values(101,'AVK IDNC',10,'John','UK',565669)

insert into dbo.orders values(102,'zasdd',5,'Micd','UK',121246)

insert into dbo.orders values(103,'kkkpp',9,'rahm','US',98595)

insert into dbo.orders values(104,'werds',45,'mlok','US',14152)

insert into dbo.orders values(105,'qazdd',1,'redss','Asia',66666)

insert into dbo.orders values(106,'zasdd',1,'jjjsl','Asia',9999)

Replication Row Filters

A static row filter uses a WHERE clause to select the appropriate data to be published. When setting up the publication, we can add a filter to only publish specific records. The below screenshot shows "orders" table which will be published.

New Publication Wizard in SQL Server

After you select the Next button, you can set a data filter by clicking the Add button.

Filter Tables in SQL Server Replication Publication

We can see the help explains how to build the filter statement.

Example Statement on how to Filter with SQL Server Replication Publication

Now we define the filter as shown below to only include records from the "UK".

Add Filter in SQL Server Replication Publication

Filter Table Rows in SQL Server Replication Publication

Once the publication was created, I created a subscription. The below shows the filtered data that was replicated. As we have created a publication to replicate records only having a CustomerAddress equal to 'UK' only those records are replicated.

Validate SQL Server Data from Replication Publication

If we add additional records as shown below, the filtered condition will only replicated the data where CustomerAddress equals "UK".

Add new records to the replicated table in SQL Server

Validate the data in the replication publication

Note: Row filters in transactional publications can add significant overhead, because the article filter clause is evaluated for each log row written for a published table to determine whether the row should be replicated. So this should be used with proper performance testing. Also, if you add a row level filter after the publication is created this will require that the subscription be reinitialized.

Replication Column Filters

In order to achieve our second requirement, to filter specific columns we need to use column filters.

We can also create column filters when the publication is being created or afterwards (but doing so afterwards will require the subscription to be reinitialized).

To achieve this, on the articles page for each article we need to select which columns to be replicated. Suppose in our case we don't want to replicate CustomerName, CustomerAddress and CustomerContactNo. So when we uncheck these columns and click "OK" this will prompt us to reinitialize the existing subscription as shown below.

SQL Server Replication Column Filters

Reinitialize Subscriptions for SQL Server Replication

Once replication has been synchronized only the selected columns will be published as shown below.

Validate final data set in SQL Server Management Studio

There are some restrictions where columns cannot be filtered:

  • Primary key columns are required for all tables in transactional publications, so they cannot be filtered.
  • All columns referenced in row filters must be included in the publication.
  • Columns with image, varchar(max), XML, UDT cannot be filtered.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Rajendra Gupta Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

get free sql tips
agree to terms