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.
msufian
SQLServerSea 
Hi,
Great guide! However, I’m stuck at the step where you do sth with the sp_addartical stored procedure.
What exactly have you modified to finally create the table at the subscriber (instead of just replicating the view)?
Did you right-click on the Publication -> Generate Script?
Thanks!
R
it will get created automatically.
Hi, thanks for getting back to me.
I mean the second-to-last section where you write “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.”
My problem is: After I added the indexed view to the transactional replication (the step before) I don’t know what to do next. If I initialise replication only the view is replicated, not the associated data.
How did you find / change the stored procedure to achieve replication of the data as well?
Thanks for your help!
Can u please let me know the senario , so that i can help in a better way
I’ve got a rather old software application that works with a data base. In the data base, some tables have no primary keys – just like the problem you describe at the top.
For reporting, I need to replicate the original data base and create a history of all transactions. Ordinary transactional or merged replication doesn’t work, because of the missing keys / the fact, that the “uniqueidentifier” column added by SQL Server in such cases will break the software working with the data base.
Snapshot replication is no good either, because the application software does accasionally delete records on the publisher DB – these Deletes shall not be replicated to the subscriber (to keep a history for reporting).
This was when I thought creating indexed views of the original tables and then replicating these as tables on the subscriber would solve my problem – which is why I came across your article here.
How do I get the views replicated as tables (with the data)?
can u send me the script for adding artical , as i don’t see any issue with my script.What i can sense is u must have not selected “Drop existing object and create a new one” in artical pulication properties or u must have set the type to “indexed view schema only”