Replicate Indexed Views as Table (Transactional Replication)

In this blog we will talk about why to create an indexed view, how to create an index view and replicate the index view as a table to existing or new Transactional Replication.

While working with one of my client running PeopleSoft Application having database on SQL Server 2005 wanted to segregate the reporting part. To do so we proposed Transactional Replication and use the subscriber database as Reporting Database.

The problem came up was number of tables doesn’t have any primary key and they were not ready to create any type of Keys on the table. So, the antonym is how to add those tables as article in the publication.

Searching the BOL we came across about Indexed view. Now the question is how we can use it.

We took one of the tables which doesn’t have a primary key and created an indexed view with SCHEMA BINDING referencing table.

SCHEMA BINDING: Binds the view to the schema of the underlying base tables.

Creating Indexed View with Schema Binding

USE <Database Name>
CREATE  VIEW Vw_Emp_SalTrans_rep WITH SCHEMABINDING
AS
SELECT EmloyeeID, SalDay, LastSalMon, SalYrs
FROM dbo.Emp_SalTrans

Once the Indexed View is created,Create Unique Clustered Index on the same

CREATE UNIQUE CLUSTERED INDEX Vw_Emp_SalTrans_rep_CluInd ON
Vw_Emp_SalTrans_rep (EmloyeeID, SalDay, LastSalMon, SalYrs)

 

Confirm that table on which view has been created exists on both the publisher and subscriber.
Doing so is a mandatory step since indexed view works on the principle of a log based operation i.e whatever DMLs are being committed on the production table gets replicated to the subscriber through the indexed view.

 

Add the indexed view as an article in existing transactional Replication

To added index view in the exiting transactional publication we will use sp_addartical stored procedure as GUI doesn’t much option to do so.

 

Generate the snasphot of the publication with re-initialize all subscription.

Once is snapshot is generated the same get applied to the subscription database.

Stored Procedure sp_addartical has lot of more option , so to explore more search for sp_addartical in BOL.