Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Replicating Indexed Views as Tables
Indexed views support the capability to be replicated as tables; in this case, SQL Server creates a table on the subscriber that contains the same data as the indexed view on the publisher. Data changes to the indexed view on the publisher are replicated to the table on the subscriber. Note that the table on which the indexed view is based does not have to exist on the subscriber.
Replicating indexed views as tables can be useful if you need only a subset of a given table’s data on the subscriber. For example, if I want to have only customers that have a birth date after 1/1/1978 on the subscriber, I can replicate the indexed view shown earlier in the article instead of replicating the entire dimCustomer table.
To replicate an indexed view as a table, you need to modify the @type parameter of the sp_addarticle system procedure. By default, this parameter has a value of N’indexed view schema only’ for indexed views; you need the value of N’indexed view logbased’. For example, the following statement View_DimCustomer_Young adds an indexed view to an existing publication to replicate as a table:
exec sp_addarticle @publication = N’publication_name’, @article = N’View_DimCustomer_Young’, @source_owner = N’dbo’, @source_object = N’View_DimCustomer_Young’, @type = N’indexed view logbased’, @description = null, @creation_script = null, @pre_creation_cmd = N’none’, @schema_option = 0x0000000008000001, /* table name doesn’t have to be the same as view name */ @destination_table = N’View_DimCustomer_Young’, @destination_owner = N’dbo’ GO
Once you configure an indexed view to replicate as a table, the INSERT, UPDATE, and DELETE statements executed against the view on the publisher will be replicated to the respective table on the subscriber.