Creating index on table at subscriber

I have a database for a 3rd party application that I need to report from.
Since I can't do anything to the 3rd party database, I am replicating the
relevant tables to my own database and running the queries there.
One of the tables (Action table) has two fields (Datestamped, Timestamped),
which are critical to some complicated queries I'm running. However neither
of these fields have indexes in the 3rd party database (since they obviously
aren't important to the 3rd party application).
I want to create indexes at the subscriber, but am worried about the
following.
1) Can I do it without breaking the replication or the databases?
2) What will the maintenance be like for this? Will I need to recreate the
indexes every time I need to rebuild the replication?
Thanks in advance
Derek
sp_addscriptexec is only good for UNC type snapshots, ie it will not work
for snapshots delivered by FTP.
The recommendation to deploy this index via a post snapshot command by Paul
is good one.
You could also deliver a custom object script using sp_addarticle, where the
custom script would have the index in it; however you may find that this
will slow down your snapshot delivery.
Here is an example of this:
http://groups.google.com/groups?selm...&output=gplain
The downside of having additional indexes on the subscriber tables is that
it will slow down update activity on your subscriber. In general minimze
your indexes on your subscriber tables. You probably have no option in your
case.
For index maintenance, auto update stats always helps (again this may lead
to some performance degradation with heavy updates), so you might want to
consider nightly dbcc dbreindex or indexdefrag.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:4a3001c490c0$9b72d050$a601280a@.phx.gbl...
> Derek,
> the easiest way to do this is to have a post-snapshot
> script to run which adds the indexes. If initialization
> has already taken place, you can add the index manually
> or using sp_addscriptexec (transactional and merge)
> without breaking the replication setup.
> HTH,
> Paul Ibison
|||I haven't been able to get this working yet, but haven't yet had the time to
investigate fully and am now knocking off for the day (in Australia).
However I'm having trouble keeping the replication going once I have changed
indexes. Today I have gotten this error:
The query processor could not produce a query plan from the optimizer
because a query cannot update a text, ntext, or image column and a clustering
key at the same time.
Is this related?
"Hilary Cotter" wrote:

> sp_addscriptexec is only good for UNC type snapshots, ie it will not work
> for snapshots delivered by FTP.
> The recommendation to deploy this index via a post snapshot command by Paul
> is good one.
> You could also deliver a custom object script using sp_addarticle, where the
> custom script would have the index in it; however you may find that this
> will slow down your snapshot delivery.
> Here is an example of this:
> http://groups.google.com/groups?selm...&output=gplain
> The downside of having additional indexes on the subscriber tables is that
> it will slow down update activity on your subscriber. In general minimze
> your indexes on your subscriber tables. You probably have no option in your
> case.
> For index maintenance, auto update stats always helps (again this may lead
> to some performance degradation with heavy updates), so you might want to
> consider nightly dbcc dbreindex or indexdefrag.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:4a3001c490c0$9b72d050$a601280a@.phx.gbl...
>
>
|||Derek,
I found this whch is relevant for your situation:
http://groups.google.com/groups?hl=e...ngxa10.phx.gbl
From BOL for Update it states "If an update query could alter more than one
row while updating both the clustering key and one or more text, image, or
Unicode columns, the update operation fails and SQL Server returns an error
message."
HTH,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||That seems to have worked.
What I have ended up doing is instead of deleting all the indexes and
creating my own, I have simply added some that I needed. Not quite as good,
but should do the job.
Derek
"Paul Ibison" wrote:

> Derek,
> I found this whch is relevant for your situation:
> http://groups.google.com/groups?hl=e...ngxa10.phx.gbl
> From BOL for Update it states "If an update query could alter more than one
> row while updating both the clustering key and one or more text, image, or
> Unicode columns, the update operation fails and SQL Server returns an error
> message."
> HTH,
> Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>