Replication of indexed views

  • Article

Recently we created a Transactional Replication for one of our customer's database.

In addition to tables, views and procedures we had indexed view including Full Text Index base on it.

Currently, we cannot specify on the management studio what we’d like to replicated with the indexed view, so the only way is to do it with a script.

We need to pay attention to the following parameters: @type and @schema_option.

There are a few options, I’ve used
@type=N'indexed view schema only' and @schema_option=0x0000000009000051

@type is quite simple, I wanted to replicate only the schema.

The @schema_option is a bitmask and in my case it contains the following bits:

· 0x01 - Generates the object creation script

· 0x10 - Generates a corresponding clustered index

· 0x40 - Generates corresponding nonclustered indexes

· 0x1000000 - Full-text index

· 0x8000000 - Create any schemas not already present on the subscriber

The command looks like this

 

 

use [PublishedDB]

exec sp_addarticle
@publication=N'DB_Publication',
@article=N'v_View',
@source_owner=N'dbo',
@source_object=N'v_View',
@type=N'indexed view schema only',
@description=null,
@creation_script=null,
@pre_creation_cmd=N'drop',
@schema_option= 0x0000000009000051,
@destination_table=N'v_View',
@destination_owner=N'dbo'
GO

conclusions

· indexed view can be replicated in a few ways – schema only or as table

· article replication has lots of options, you can read about them here