Indexed Views - Clustered Index is NOT replicated (not scripted by

[jk]

Using SQL 2005 SP2.
I have a publication that contains indexed views, and some other objects
that query the indexed view using WITH (NOEXPAND). Currently replication
fails because the CLUSTERED INDEX on the view is NOT replicated. I've
experimented with various schema options but nothing changes. The view is
replicated but not the clustered index on that view.

I've seen some discussion on replicating indexed views to a table, but I
would like to replicate indexed view schema fully. (Including the clustered
index on that view).
Is there a way to make this work?

Thanks.

[Ben Thul]

Can you post the sp_addarticle invocation that you used? I'm
specifically interested in what you used for the @type and
@schema_option parameters. Thanks!
--
Ben

[jk]

Ben, I've used a few, leaving status at default.

exec sp_addarticle @publication = N'all_objects', @article =
N'ImpactCost_KD_WI', @source_owner = N'dbo', @source_object =
N'theIndexedViewArticle', @type = N'indexed view schema only', @description =
N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option =
0x0000000008000011, @destination_table = N'ImpactCost_KD_WI',
@destination_owner = N'dbo', @status = 16

I've also tried @schema_option = 0x0000000008000041 and @schema_option =
0x0000000008000051 with no success.

Thanks.

"Ben Thul" wrote:

> .
>

[Ben Thul]

I would have thought that setting the 0x10 bit in the @schema_option
parameter would have done the trick, but apparently not. One thing
that I noticed from BOL is that with @type = N'indexed view schema
only', the base tables also need to be published. Is this the case?
Regardless, have you tried @type = N'indexed view logbased'?
--
Ben

[jk]

Yes, according to BOL that schema option should have worked. I suspect it may
be a 'feature'. I did not try 'indexed view logbased' because, as I've
mentioned, I do not want a table on the subscriber - I want the view.

[Ben Thul]

I'm running out of ideas, but I did come up with a proof of concept
that it is possible to replicate a view with its clustered index.
Here's the script:
/*******************
BEGIN SCRIPT
*******************/
use <publisher_db, sysname,>
go
if (select object_id('dbo.my_view', 'V')) is not null
drop view dbo.my_view
go
if (select object_id('dbo.base_table', 'U')) is not null
drop table dbo.base_table
go
create table base_table (
a int not null PRIMARY KEY,
b varchar(40)
)
go

create view my_view
with schemabinding
as
select a, b
from dbo.base_table
where a > 5
go
create unique clustered index idx_my_view on my_view (a)
go
exec sp_replicationdboption
@dbname = '<publisher_db, sysname,>',
@optname = 'publish',
@value = 'true'
exec sp_addpublication @publication = 'test_pub',
@status = 'active'
exec sp_addpublication_snapshot @publication = 'test_pub'

exec sp_addarticle @publication='test_pub',
@article = 'base_table',
@source_table = 'base_table',
@destination_table = 'base_table',
@type = 'logbased',
@schema_option = 0x000000000803509F,
@destination_owner = 'dbo',
@source_owner = 'dbo'
exec sp_addarticle @publication='test_pub',
@article = 'my_view',
@source_table = 'my_view',
@destination_table = 'my_view',
@type = 'indexed view logbased',
@schema_option = 0x0000000008000011,
@destination_owner = 'dbo',
@source_owner = 'dbo'
exec sp_addsubscription @publication = 'test_pub',
@subscriber = 'rvntestsql04\dbaonly',
@destination_db = '<subscriber_db, sysname,>',
@article = 'all',
@sync_type = 'automatic',
@subscription_type = 'push'
/*******************
END SCRIPT
*******************/
Of course, just like with all code you find on the internet, don't run
it in production, understand it before you run it, etc. But when I
ran this on my test server, the view was replicated with the clustered
index. I'd be curious to see if you meet with the same fate on your
system. If you do, I'd then be curious to see what differences there
are between the publications and articles.
--
Ben

[jk]

Thanks, Ben. Your test worked, except as I expected, per the @type = 'indexed
view logbased' in your script, it created a TABLE on the subscriber instead
of the VIEW I wanted.

Jan.

"Ben Thul" wrote:

> .
>

[Ben Thul]

That's my fault...I saw the same behavior. When I did it with @type =
'indexed view schema only' did create the indexed view.
--
Ben

[jk]

You are right. It does seem to work. I did it on the same database (and
server), and it worked, while the other articles dont. It has to be some sort
of glitch because all the settings are the same. Will have to experiment some
more as this test shows that it should work.

"Ben Thul" wrote:

> .
>

[Ben Thul]

One thing that I thought of...try creating the view manually at the
subscriber. Maybe there's some weird condition that prevents the
indexed view from being created there. If that's the case, it could
be detected by replication and lead to the situation you're seeing.
Just a thought...
--
Ben

[Antenaina Ramahazosoa]

Hi All,have you find the solutions for this,i got the same problem