Home > Replicate Indexed Views, SQL Server Replication > Replicate Indexed Views as Table (Transactional Replication)

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.

 

 

 

  1. Ron
    October 23, 2010 at 1:48 pm

    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

    • msufian
      October 23, 2010 at 8:33 pm

      it will get created automatically.

  2. Ron
    October 24, 2010 at 8:48 am

    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!

    • msufian
      October 24, 2010 at 7:47 pm

      Can u please let me know the senario , so that i can help in a better way

  3. Ron
    October 24, 2010 at 9:17 pm

    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)?

    • msufian
      October 25, 2010 at 3:42 pm

      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”

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: