Thursday, March 8, 2012

Adding new index to existing table in Merge Replication

I see from other posts that sp_addscriptexec is recommended for
updating/creating indexes. It isn't clear to me if I can use Enterprise
Manager to go into the table on the publisher and add additional indexes on
a table and then have the added indexes automatically populate to the
subscribers.
Additionally, BOL says that an index can contain upto 16 fields but they
recommend upto three. Does that mean I should create many indexes with only
upto three fields in each index? Help or references on this matter would
be greatly appreciated.
WB
The index can be added at the publisher as per usual. It won't get
automatically propagated to the subscribers and that is what
sp_addscriptexec is for. If I just have one or two subscribers then I tend
to create the index by hand, but if you have lots, then it is a way or being
sure they all receive the changes, especially if they are pull
subscriptions.
As for the size, this seems a general question to do with indexes. Have a
look for clustered, nonclustered and covering in BOL. Indexes are sorthed on
the first column, then for duplicates of the first column they are sorted on
the second and so on, so 2 indexes with 3 columns is not the same as one
index with 6 columns. An index with 6 columns will be 'wide' and therefore
slow, but on the other hand it might have been created as a covering index
for a particluar long-running query. This is a really big subject and I'm
sure there are loads of resources on it apart from BOL.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

No comments:

Post a Comment