Clustered Index on View Doesnt Replicates Default : Replication in SQL Server


We all know clustered index on a table is part of replication as default option but in case of views its not true. We have to add it manually.

i.e. “default schema option for an indexed view does NOT include clustered index” In GUI we don’t have option to add cluster index on view using GUI so we have to use script to do same.

To replicate the indexes, you could change script for sp_addarticle and replace @schema_option from 0x0000000008000001 to 0x0000000008000051. The following example allows both clustered and non-clustered index to be generated at the subscriber:

exec sp_addarticle
@publication = N'pub1',
@article = N'view1',
@source_owner = N'dbo',
@source_object = N'view',
@type = N'indexed view schema only',
@description = N'',
@creation_script = N'',
@pre_creation_cmd = N'drop',
@schema_option = 0x0000000008000051,
@destination_table = N'view',
@destination_owner = N'dbo',
@status = 16

NOTE: You will need to drop/re-add the subscription in order to drop/re-add the index of view if view is already part of replication.


Reference: https://msdn.microsoft.com/en-us/library/ms173857.aspx

1 comment: